CODEWITHSUNDEEP
sqlsql-servert-sql
Rob
Rob

Reputation: 1255

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), 1, '') 

    print @cleanline

Thanks Rob

Upvotes: 1

Views: 586

Answers (1)

gbn
gbn

Reputation: 432261

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

Upvotes: 3

Related Questions