Sunday, January 05, 2020

SQL SERVER Clean String function

SQL SERVER Clean String function



Create Function [dbo].[CleanString] (@input nvarchar(max))
Returns nvarchar(max)
As
Begin


declare @a nvarchar(max)
declare @b nvarchar(max)
Set @a=@input
Set @b = Replace(REPLACE(@a, SUBSTRING(@a, PATINDEX( '%[,~,@,#,/,\,(,),$,%,&,*,(,),+,.,]%', @a), 1 ),''),'-','')
Set @a=@b
Set @b = Replace(REPLACE(@a, SUBSTRING(@a, PATINDEX( '%[,~,@,#,/,\,(,),$,%,&,*,(,),+,.,]%', @a), 1 ),''),'-','')
Set @a=@b
Set @b = Replace(REPLACE(@a, SUBSTRING(@a, PATINDEX( '%[,~,@,#,/,\,(,),$,%,&,*,(,),+,.,]%', @a), 1 ),''),'-','')
Set @a=@b
Set @b = Replace(REPLACE(@a, SUBSTRING(@a, PATINDEX( '%[,~,@,#,/,\,(,),$,%,&,*,(,),+,.,]%', @a), 1 ),''),'-','')
return @b

End


Call Function on Select statement :

Select top 1 DataEntryDate ,   dbo.CleanString('A-SMN~dfs&d-fdsh/adsfdsf/sdfsdf\sdf')   from TableName