Search code examples
sqlsql-serversql-function

SQL Function Replace Non-numeric Values

What I'm trying to do is create an SQL function where it checks if a number has any non-numeric characters and if it does remove them. But if the number has a +1234, leave it. So some number have 1234 and some have have +1234. I dont want to add + to all the numbers.

Here is my SQL function below:

CREATE FUNCTION[dbo].[fn_NonNumericCharacters] ( @strText varchar(1000) )
RETURNS VARCHAR(1000)
AS
BEGIN
WHILE PATINDEX('%[^+0-9]%', @strText) > 0
BEGIN
    SET @strText = STUFF(@strText, PATINDEX('%[^+0-9]%', @strText), 1,    '')
END
RETURN @strText
END

What I'm trying to do with this function is make it look for these characters ()-,.SPACE .

What do you think?

Solution

  • Store the first char if its a +, replace +, re-append, return:

    CREATE FUNCTION[dbo].[fn_NonNumericCharacters] ( @strText varchar(1000) )
RETURNS VARCHAR(1000)
AS
BEGIN
  DECLARE @prefix VARCHAR(1) = CASE WHEN @strText LIKE '+%' THEN '+' ELSE '' END

  WHILE PATINDEX('%[^0-9]%', @strText) > 0
      SET @strText = STUFF(@strText, PATINDEX('%[^0-9]%', @strText), 1,    '')

  RETURN @prefix + @strText
END