SQL Tips by Namwar Rizvi

September 5, 2007

Validating Inputs and Finding Patterns with PatIndex

Filed under: string manipulation,tips,TSQL,Utility Functions — namwar @ 8:54 PM

PatIndex is one of most powerful functions of TSQL but often developers ignore it firstly because of the availability of CharIndex and secondly, the difficulty of creating patterns for PatIndex to search. Developers try to solve some typical problems faced by today’s web application by developing their own workarounds instead of using PatIndex.
Today, I will share some very useful tricks which can help you to make sure that your input string is according to the pattern you want like all numeric, all letters, starts with letter and end with number etc.

Following are the TSQL scripts which you can use to solve these type of problems. You can convert them to user defined functions as per your requirements:

—Checks that input only contains numbers

if PatIndex(‘%[^0-9]%’,’11’) > 0
Print ‘Not all numbers’

Print ‘All numbers’

—Checks that input only contains letters
if PatIndex(‘%[^a-z]%’,‘aaaaa’) > 0
Print ‘Not all letters’

Print ‘All letters’

–Checking for mixed input
If PatIndex(‘%[^0-9][0-9]%’,‘abc’) > 0
Print ‘Alpha numeric data’

Print ‘Either all numbers or all letters’

–Checks that value must start with a letter and a number
If PatIndex(‘[^0-9][0-9]%’,‘A1anamwar11’) > 0
Print ‘Starts with a letter and a number’

Print ‘Does not start with a letter and a number’

–Checks that value must End with a letter and a number
If PatIndex(‘%[^0-9][0-9]’,‘A1anamwar11a1’) > 0
Print ‘Ends with a letter and a number’

Print ‘Does not End with a letter and a number’

–Checks that value must Start with a letter and Ends with a number
If PatIndex(‘[^0-9]%[0-9]’,‘namwar1’) > 0
Print ‘Starts with a letter and ends with a number’

Print ‘Does not start with a letter and ends with a number’


August 24, 2007

Number padding in TSQL

Filed under: string concatenation,string manipulation,tips,TSQL — namwar @ 9:24 PM

Sometime we need to pad leading zeros to numeric
values so that they can appear having same width
like 00001,0022,0934 etc.
Following code demonstrates the technique
to pad leading zeros in front of digits.

–Variable to hold max length
Declare @m_maxLength int

–Create a test table
Declare @m_testTable table (sampleValue int)

–Insert some sample values
Insert into @m_testTable values (1)
Insert into @m_testTable values (2)
Insert into @m_testTable values (19)
Insert into @m_testTable values (201)
Insert into @m_testTable values (20231)

–Calcualte the length of maximum number in the table
from @m_testTable

—Now select the values
convert(varchar,sampleValue) as paddedValue

from @m_testTable

June 29, 2007

Arrays in SQL Server 2005

Arrays are one of the most popular data structures for several solutions. Unfortunately, SQL Server does not support arrays in TSQL. As an alternative, you can use Table variable or temporary tables as a work around for arrays.
During my research about various possible approaches for array implementation, I found following article very interesting and informative, I will suggest to all my readers to read this.
Article is available here as Arrays in SQL Server 2005

May 31, 2007

Finding all mispelled string values quickly

Filed under: Information,Query,string manipulation,tips,TSQL — namwar @ 9:10 PM

During data cleansing of imported data from some legacy system, we find that some values are actually spelled incorrectly and therefore, they are not getting included in the result of a query. Finding all the misspelled versions of a given value is quite difficult if you have thousands or millions of records.
Fortunately, there is a quick and easy solution for it which is “SOUNDEX” function in TSQL. Please note that Soundex is not the guarnteed way of finding all the incorrect versions but it is one of the quickest and nearly 90% accurate way of it.
Soundex is an algorithm and it bases on the idea that similar sounding words will have a same alpha-numerical score calculated by this algorithm. So for example, if you have a column called “Color” and you have different variation of same color names like Red,Redd,Redh etc. then Soundex will assign the same score to all of them and you can easily find these variations by comparing the Soundex score. Following is the full working example to better understand this concept.

–Disable SQL Server intermediate messages
Set NoCount On

–Create test Table containing daily data
Declare @m_TestTable table (ItemId int, Color varchar(50))

–Insert some sample values
Insert into @m_TestTable values(1,‘Red’)
Insert into @m_TestTable values(2,‘Reddh’)
Insert into @m_TestTable values(3,‘Redd’)
Insert into @m_TestTable values(4,‘Blue’)
Insert into @m_TestTable values(5,‘Green’)
Insert into @m_TestTable values(6,‘Dark Red’)

—Select all those items which are Red
Select * from @m_TestTable Where Soundex(Color)=Soundex(‘Red’)

May 14, 2007

Case Sensitive string comparison in SQL Server 2005

Filed under: Query,SQL Server 2005,string manipulation,tips,TSQL — namwar @ 8:35 PM

Normally most people install SQL Server with default collation which means SQL Server will compare strings by ignoring their case. If you want to perform a case sensitive query without changing the collation of your database environment then you need to specify the collation in your query. Following is the sample code to demonstrate how you can easily perform a case sensitive query in SQL Server 2005:

–Disables intermediate sql server messages
Set Nocount on

–Declare a test table for our example
Declare @m_TestTable table ( Id int, Name varchar(50) )

–Insert two sample records
Insert into @m_TestTable (id,Name) Values (1,‘London’)
Insert into @m_TestTable (id,Name) Values (2,‘london’)

–Perform a normal case insensitive search
Select * from @m_TestTable Where Name=‘London’

—Perform same search but with case sensitive search option
Select * from @m_TestTable Where Name=‘London’ COLLATE SQL_Latin1_General_Cp1_CS_AS

Blog at WordPress.com.