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

Clear Validation Errors and Validation Summary messages

ASP.net built in validation does not provide us a straight forward to clear all the validation errors. This would be really helpful while resetting a form. The reset html button would simply reset the form values but will not clear the validation errors. The following javascript code snippet can be used to clear the validation error messages. Have a reset button on your form and call the following js function onclick. <input type="reset" onclick="HideValidationErrors();" /> function HideValidationErrors() { //Hide all validation errors if (window.Page_Validators) for (var vI = 0; vI < Page_Validators.length; vI++) { var vValidator = Page_Validators[vI]; vValidator.isvalid = true; ValidatorUpdateDisplay(vValidator); } //Hide all validaiton summaries if (typeof (Page_ValidationSummaries) != "undefined") { //hide the validation summaries

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

Creating a Windows Task Scheduler Service

Recently, one of my friends asked me for help in creating a windows service for scheduling some tasks. He said that he could not use the task scheduler that comes with Windows as the Task Scheduler expects each task to be a stand alone executable file. All his tasks were in a single class library and it would be a lot of work to separate each task into its own executable. Also he said that that the schedule for the tasks could change from time to time and it would be nice if he could configure the tasks in an XML file. To Recap, the requirements for the windows service are 1) The tasks should be loaded from a class library 2) The schedule information for the tasks should be configurable in an XML file. We googled (binged :)) for the solution and found an excellent article by Ajit Kumar Application Scheduler Service Using C#.Net And XML to base our solution upon. We took some good points like configuring the task information from the above mentioned article. This is how we to

Kode's thoughts

Search This Blog