SQL Function Replace Non-numeric Values

Question

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?

Does it do the job you want it to do? If so, Code Review might be a better site to ask on. — Damien_The_Unbeliever
– Damien_The_Unbeliever, Commented Jul 31, 2015 at 13:07
Do you only want to replace these characters: ()-,.SPACE? Performance-wise, a nested replace should be faster. — Hart CO
– Hart CO, Commented Jul 31, 2015 at 13:07
Im not sure if it will work yet, i was just wondering id this line 'SET @strText = STUFF(@strText, PATINDEX('%[^+0-9]%', @strText), 1, '')' , while make all number +1234 ? — AndroidAL
– AndroidAL, Commented Jul 31, 2015 at 13:11

Alex K. · Accepted Answer · 2015-07-31 13:15:26Z

1

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

answered Jul 31, 2015 at 13:15

Alex K.

177k32 gold badges276 silver badges299 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Tom Over a year ago

CASE WHEN LEFT(@strText,1) = '+' may be a bit faster.

AndroidAL Over a year ago

Thanks so, that looks for '+', then replaces it?, but i dont want to replace it.

Alex K. Over a year ago

Yes, but then it restores the leading + if the input began with + so '+123+4Z56' => '+123456'

Collectives™ on Stack Overflow

SQL Function Replace Non-numeric Values

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related