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

Cool Expensive Headphones

I came across this website when I was reading a blog. Ultimateears is supposed to be one of the best headphones out in the market. Their custom fit ear phones looks completely wierd and the price is expensive.. a cool 900 $ ( yup I meant Nine Hundred dollars). I hope to try these headphones one day just to see is it really worth the money :)

Web 2.0

Here's a nice collection of all the best 2.0 web software that are currently available on the net.

http://web2.wsj2.com/the_best_web_20_software_of_2005.htm

I was really happy to see one of my favourite sites del.icio.us noted as one of the best social bookmarking sites. I am going to try all the other cool sites (mainly voo2do and calendarhub) will present my thoughts on them as a blog entry soon.

Different Architecture Roles

I was always confused to hear people mixing the following terminologies
System Architect, Enterprise Architect, Solution Architect and Application Architect.

In many small companies, all these positions will handled by one person with the tag "Technical Architect". In bigger companies since a single person can not have expertise in all these fields, they have seggergated into more specfic chunks.

Enterprise Architect : One who takes care of the systems integration at the enterprise level. They are very superficial and do not have any techinical know how. They are just interested in the integration of the products at the department level and adherence of all projects to the corporate system standards.
Application Architect : One who knows the product in and out. He may not be strong in technical details but understands the concept.
Solution Architect : The guy who designs and makes the descions and presents the solution with low level technical details.
Systems Architect : The guy who basically knows how to configure machines and servers and have indepth knowledge of opertating systems and all other hardware realted stuff.

Manning does it at last

Boy !! Did he do it in style :) I am talking about Peyton Manning's Monday night performance against New England Patriots. The colts thrashed patriots all night, right from the word go I think it was clear that Colts are a far superior team than Patriots this year. Personally, having supported Indianapolis for the last two years, I have got increasingly frustrated with the way they always lose to Patriots in Foxborough year after year. At last it feels good to see the bad karma (beating Patriots in New England) being taken out of Manning's career which has otherwise being absolutely amazing.

The Patriots have always been a good team under Bill Belichek and Tom Brady as their QB. But after some time it gets real boring to see the same team win the super bowl over and over again. For a change it was nice to see a frustrated Tom Brady and coach Belichek throwing the red flag to challenge a clear Touchdown pass.

Installing Windows 2003 on Inspiron 700m

I have been having a tough time installing Windows 2003 on my Dell Inspiron 700m. The installation will proceed for some time and will freeze exactly at the 34 minute mark and I couldnt find help anywhere. Till I came across this article that explains clearly the steps need to be taken for installing windows 2003..

Now let me get back on track to install the Team Foundation Server (beta) on my laptop. I have been trying to play around with Team Foundation for a long time after reading the cool features it contains. Will keep posting about anything cool I come across.

Linq (Languange INtegrated Query)

I had been looking at some resources to start my quest in C# 3.0(especially LINQ). I came across this channel9 video on Linq by Anders Hejlsberg.

It was a nice video with samples that will help you get upto speed on Linq. I am surprised that guys in Microsoft keep coming up with some cool ideas for every new release.

Everyone will agree that there is a big gap between the relational world and object world. I think Microsoft is trying to reduce that Gap with LinQ. I can already see many ways of implementing Linq in many areas in my project.

VS.Net Tip(s) of the Day

I came across some cool tips for use inside VS.Net.

1. A Cool feature of VS.NET is its ability to launch multiple projects and have a debugger attached to them. If I have an application which is using .NET Remoting, so I have a remoting host, which is an exe, I have a client who’s is going to launch remote objects. Also I’m going to have them in separate exes. I can normally run only 1 Exe at a time within the same solution. And so typically what I have to do is after I launch the client I have to go to the host and explicitly start the host to get the second exe I need to work with and then the two are actually going to be talking with each other.
Now this is kind of tedious. What I can do instead is I could go to the solution properties, select multiple startup projects and select the projects I want to start, in which case I want to start the host and I want to start the client. I can even change the order in which the client or the host are going to be started. Now when I click okay if I click the go button VS.NET is going to launch the two exes and it’s going to attach itself to them so I’m going to have the same debug session for both exes. Very handy and useful when dealing with multiple exes.


2. If I try to compile a new project and deliberately add some compilation errors to i. After trying to build, If I look at the task list I'm going to see that the errors of compilation is being added as a task for me to fix. I can also explicitly go and add a task in my task list. Now you can also do // and type to do. And it turns out that to do is a recognized token by VS.NET and the moment you say to do it would add that line to the task list. And you can actually your own constants and your own token to the task list. If you go to tools, options, task list you can add your own custom Task List. Having your own custom task list will be helpful when you’re looking for other things to do in your project you don’t have to literally do a search in all files and look for to dos. Just look at the task list, it will show you all the things you need to do.

3. In C# only there’s something called pre and post build action. This allows you to run a command line before and after a build. I want to do some pre and post-build action in that class library. So if I go to the project properties I have build Events. This command line, the pre-build defense is going to be executed before the build starts. A typical thing to do in the prebuild is to do a clean build, meaning remove all bin folders. A post-build allows you to run something after the build. A typical thing you want to do is you’re going to maybe install this class library in the GAC on every build.

I read these tips from an MSDN article by Jual Lowy the Great(yup ... a Software Legend too). I found it very useful.

Tip of the Day

I found this really cool tip for keyboard users in Visual Studio .Net.

If you go to the find combobox (Ctrl+D with the default keyboard mapping) and you type "> open" (without quotes). The ">" flips the find combobox into a mini Command window. The "open" part is the aliased command to the "file open" action. The super part is when you press space and the first character of the file you want to open anywhere in your solution, a dropdown window pops up with the files and folders that start with that letter. (thanks to Peter Provost)