In this post, How to Remove All Characters From a String Using T-SQL? we have seen How to Remove All Characters From a String Using T-SQL. But it only removes characters from the string. What if the string has some other special characters? Here is the method that handles this
SET NOCOUNT ON
DECLARE @loop INT
DECLARE @str VARCHAR(8000), @output VARCHAR(8000)
SELECT @str = 'ab123ce23,4f$e', @output=''
SET @loop = 1
WHILE @loop < LEN(@str)
SET @output=@output+CASE WHEN ASCII(SUBSTRING(@str,@loop,1)) BETWEEN 48 AND 57 THEN SUBSTRING(@str,@loop,1) ELSE '' END
SET @loop = @loop + 1
Now here is the result:
The idea here is instead of finding and removing non numerics, find only numbers and concatenate them. You can use if the ASCII value of a string is between 48 and 57 (which is a digit from 0 to 9). If it is in the range, it is a digit then concatenate it. This way we can remove anything which is not a number.
Reference: Pinal Dave (https://blog.sqlauthority.com)