String cleaning with TSQL

There have been some posts on cleaning text with TSQL, Remove all non-numeric characters from a string and extract-only-numbers-from-a-string

Here is my take on it. The nice thing is that you just specify the valid characters in the like pattern to select those that you want.return.

declare @string varchar(200)

set @string = 'this $%^^&is%^& s2342om23&&({}e c76l232e+_+a#n/ c][#o''d#e'

 

select cast(cast((select substring(@string,n,1)

from num

where n <= len(@string)

and substring(@string,n,1) like '[a-z ]' for xml path('')) as xml)as varchar(max))

 



-
Published Tuesday, February 26, 2008 1:35 PM by simonsabin
Filed under:

Comments

Tuesday, February 26, 2008 3:04 PM by Alex_Kuznetsov

# re: String cleaning with TSQL

Great! I love it!

Wednesday, February 27, 2008 8:50 AM by Madhivanan

# re: String cleaning with TSQL

Good one Simon Smile

Sunday, January 18, 2009 5:38 PM by Looping options | keyongtech

# Looping options | keyongtech

Pingback from  Looping options | keyongtech