Saturday, January 13, 2007

Using CASE, WHEN in SQL Server 2000

CASE expression in SQL Server is very useful and can save you hours of time.

In my project, I had to update a column in a table with several thousands of rows. My task is to remove a prefix (ANC) from the data in the column.
For example, the data in the column looks like below. I want to remove ANC and the following hyphen or space from all the rows in that column.
ANC-1E01
ANC2B02
ANC 3C01
3C01
2E
Null

My intial thought was to update the table manually, but with the sheer amount of rows in the table that approach seemed humungous.

Second thought was to write a cursor to loop through the table and update each row. But I thought there should be a better approach.

On brainstorming a bit, the CASE expression in SQL Server came to my rescue. It offered me a simple and elegant solution.

Here is how I updated my table.


UPDATE WARDDetails
SET Ward=
CASE
WHEN charIndex('ANC-',Ward)>0 --If the data is like ANC-XXX remove 'ANC-'
THEN SUBSTRING(Ward,charIndex('ANC-',Ward)+4,len(Ward))
WHEN charIndex('ANC ',Ward)>0 --If the data is like ANC XXX remove 'ANC '
THEN SUBSTRING(Ward,charIndex('ANC- ',Ward)+4,len(Ward))
WHEN charIndex('ANC ',Ward)>0 --If the data is like ANCXXX remove 'ANC'
THEN SUBSTRING(Ward,charIndex('ANC',Ward)+4,len(Ward))
ELSE Ward --If none of the above, dont update

END

Digg It! Add to del.icio.us Stumble This

0 Comments: