Skip to main content

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

Comments

Post a Comment

Popular posts from this blog

Find the cause of poor performance in Sql Server

I found the following two part article by Gail Shaw on Simple-Talk really helpful in trouble shooting poorly performing queries in Sql Server. The articles talks about spotting poorly performing queries with the help of the Profiler, understand Sql Server Query plans and fine tune the peformance using proper indexes. Part 1: http://tinyurl.com/ccl6gj Part 2: http://tinyurl.com/okcuqg
18 comments
Read more

Kill a remote user session remotely

When trying to connect to your Windows 2000/2003 server remotely, you may receive the following error. "The terminal server has exceeded the maximum number of allowed connections." You could kill one or more of those connections by using PsExec tool that can be downloaded from the following link. This tool and a bunch of others were developed by SysInternals which was bought by Microsoft. http://www.microsoft.com/technet/sysinternals/utilities/pstools.mspx Open your command prompt and from the directory that contains the psexec utility, do the following 1) psexec \\x.x.x.x -u user -p password cmd (this will give you access to the cmd prompt on the server) Example: psexec \\127.0.0.1 -u admin -p password cmd 2) once you get the command prompt run the command qwinsta to get a list of all Terminal Services connections. Each connection has an Id Number. 3) Run the command logoff [id# of session to quit] /v (this will kill the connection with that id #) Example: logoff 2 /v Once ...
4 comments
Read more

SharePoint: Comparing dates in XSLT

In one of my sharePoint projects, I had to compare a date with the current date in XSLT. As it turned out, there is no support for date comparision in XSLT. This is how I went about doing it. The trick involves converting the date string into a number Say the date is in ISO format YYYY-MM-DDTHH:MM:SSZ (for example, 2001-01-21T22:54:47Z). Say we have a variable DueDate <xsl:variable name="DueDate" select="'2001-01-21T22:54:47Z'"> Replace the dashes in the string with empty string Take the first 10 character in the date sring convert the string to number number(translate(substring(@DueDate,1,10),'-','')) On doing the above we get: 20010121 We can do the same thing with the date to compare. We can compare the duedate with the current date in SharePoint as follows number(translate(substring(@DueDate,1,10),'-','')) & lt; number(translate(substring(ddwrt:TodayIso(),1,10),'-','')) You can apply the same log...
2 comments
Read more