Stripping unwanted chars from a string in MS SQL
I have some code below which returns a clean string
How would retain commas in the output
select @cleanline=line from #tbl
While PatIndex('%[^A-Z,a-z,0-9]%', @cleanline) > 0
Set @cleanline = Stuff(@cleanline, PatIndex('%[^A-Z,a-z,0-9]%', @cleanline),开发者_JS百科 1, '')
print @cleanline
Thanks Rob
Change %[^A-Z,a-z,0-9]%
to %[^A-Z0-9]%
or%[^A-Za-z0-9]%
By default, SQL Server isn't case sensitive and you don't need commas in pattern-match strings
Edit: turns out there are no commas in the data...
select @cleanline = line from #tbl
set @tokeeppattern = '%[^A-Z,a-z0-9' + CHAR(9) + ']%'
While PatIndex(@tokeeppattern, @cleanline) > 0
Set @cleanline = Stuff(@cleanline, PatIndex(@tokeeppattern, @cleanline), 1, '')
print @cleanline
精彩评论