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
Begin
Print ‘Not all numbers’
End
Else
Begin
Print ‘All numbers’
End
—Checks that input only contains letters
if PatIndex(‘%[^a-z]%’,‘aaaaa’) > 0
Begin
Print ‘Not all letters’
End
Else
Begin
Print ‘All letters’
End
–Checking for mixed input
If PatIndex(‘%[^0-9][0-9]%’,‘abc’) > 0
Begin
Print ‘Alpha numeric data’
End
Else
Begin
Print ‘Either all numbers or all letters’
End
–Checks that value must start with a letter and a number
If PatIndex(‘[^0-9][0-9]%’,‘A1anamwar11′) > 0
Begin
Print ‘Starts with a letter and a number’
End
Else
Begin
Print ‘Does not start with a letter and a number’
End
–Checks that value must End with a letter and a number
If PatIndex(‘%[^0-9][0-9]‘,‘A1anamwar11a1′) > 0
Begin
Print ‘Ends with a letter and a number’
End
Else
Begin
Print ‘Does not End with a letter and a number’
End
–Checks that value must Start with a letter and Ends with a number
If PatIndex(‘[^0-9]%[0-9]‘,‘namwar1′) > 0
Begin
Print ‘Starts with a letter and ends with a number’
End
Else
Begin
Print ‘Does not start with a letter and ends with a number’
End
