How To Remove Characters & Special Symbols From String Using SQL Function

This blog imparts knowledge on how to remove characters & special symbol from the string, using SQL function.

Many developers take too much time to do small tasks in SQL Server.   

Create function [dbo].[RemoveCharSpecialSymbolValue](@str varchar(500))
returns varchar(500)
begin
declare @startingIndex int
set @startingIndex=0
while 1=1
begin
set @startingIndex= patindex('%[^0-9.]%',@str)
if @startingIndex <> 0
begin
set @str = replace(@str,substring(@str,@startingIndex,1),'')
end
else break;
end
return @str
end

Explanation

In this function, set @startingIndex for the first time to 0 after which, use while loop and get the index where numeric values are available, if it finds any characters and symbols, then it replaces only the greater numeric values.

finally it returns numeric values. 

 

Suppose you have a string, as shown below.

 

@Spcialtext nvarchar(100)='23!3fg@#56'

 

Now, use the function given below.

 

select dbo.RemoveCharSpecialSymbolValue(@Spcialtext) 

 

Output - 23356