Tuesday, October 17, 2006

Removing Special Characters from a column in SQL Server

Recently I got an interesting SQL Server puzzle from one of my collegues. The question goes like this. There is an existing table containing UserId and password and we need write a query to remove any special characters from the UserId columnin the table. My first instinct was to write a cursor to acheive the same. But obviosuly cursors are always associated with performance issues so we need come up with someother solution. So I started writing some code and googled for some ideas. Then I cameacross a neat trick in a website (I forgot the link but I would like to appreciate whoover wrote this piece of code)

Declare @TmpTable table (UserId Varchar(30))
Insert Into @TmpTable values ('pr_()em%')
Insert Into @TmpTable values ('r*^#am)')
Select 1
While @@Rowcount > 0
Update @TmpTable Set UserId = Replace(UserId, Substring(UserId, Patindex('%[^a-zA-Z0-9]%', UserId), 1), '') Where Patindex('%[^a-zA-Z0-9]%', UserId) <> 0
Select * from @TmpTable

I am sure anyone who has been working in SQL server for some time would this piece of code :). Check out how cleverly @@rowcount has been used to run the update statement recursively in a loop.

9 comments:

Arvind said...

Arvind: Man,,it was awesome,,,,thanks a lot mate

Unknown said...

This is juicy code...

Anonymous said...

http://www.nigelrivett.net/RemoveNonNumericCharacters.html

Anonymous said...

thank you

Anonymous said...

This code just save me several hours. I am overjoyed

Anonymous said...

Thanks alot

Anonymous said...

dude u save me

Anonymous said...

Pretty nice post. I simply stumbled upon your blog
and wanted to say that I've really enjoyed browsing your weblog posts. In any case I will be subscribing in your feed and I am hoping you write again very soon!
My webpage :: online casino gaming

Anonymous said...

thank u prem's for ur post

Loading...