开发者

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
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜