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.

Move to a new company

Having worked in healthcare domain for the last 3 years I decided couple of months back to move into a different area. After some serious thinking I landed in a company providing financial solutions. I just attended thier 3-day induction program and the line of products they offer is pretty interesting. I was always interested in knowing more about how electronic transactions happen from the time a member swipes his card in the ATM/Point of Sale (POS) device. I think I am in the right place to know the answer.

Still trying to get adjusted to the new place/collegues :) after seeing the same familiar friendly faces for the last 3 years. I sure miss my old collegues a lot but that's the way life goes...