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)')
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.