Post
Ask Question

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

Many developers take too much time to do small tasks in SQL Server.   
  1. Create function [dbo].[RemoveCharSpecialSymbolValue](@str varchar(500))  
  2. returns varchar(500)  
  3. begin  
  4. declare @startingIndex int  
  5. set @startingIndex=0  
  6. while 1=1  
  7. begin  
  8. set @startingIndex= patindex('%[^0-9.]%',@str)  
  9. if @startingIndex <> 0  
  10. begin  
  11. set @str = replace(@str,substring(@str,@startingIndex,1),'')  
  12. end  
  13. else break;  
  14. end  
  15. return @str  
  16. 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