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
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

Advertisements

3 Comments »

  1. I only recently found out about the PATINDEX() function (last week). Can you tell me what is the advantage of using PATINDEX() over the SQL LIKE clause? In my preliminary tests, they seem to perform in the same way.

    For example, is there a benefit to using this:

    If PatIndex(‘[^0-9]%[0-9]’,’namwar1′) > 0

    … over:

    If ‘namwar1’ LIKE ‘[^0-9]%[0-9]’

    The other day, someone said that PATINDEX() was much more efficient than LIKE, but I am looking for second opinions.

    Comment by Ben Nadel — September 24, 2007 @ 1:00 PM | Reply

  2. What does If PatIndex(’[^0-9]%[0-9]’,’namwar1′) > 0
    or If ‘namwar1′ LIKE ‘[^0-9]%[0-9]’ do???
    Thanks.

    Comment by Vicky — February 14, 2008 @ 5:36 PM | Reply

  3. PATINDEX and LIKE do different things. PATINDEX is very handy for parsing strings in tandem with SUBSTRING. Here is a good TRIM function example.

    CREATE FUNCTION [dbo].[udf_Trim] (
    @String nvarchar(4000)
    ,@NullIfEmpty bit = 1)

    RETURNS nvarchar(4000) AS

    BEGIN

    DECLARE
    @charIndex smallint
    ,@search_pattern nvarchar(100)
    ,@length smallint
    ;

    SELECT
    @search_pattern = ‘%[^’ + CHAR(10) + ‘, ‘ + CHAR(13) + ‘, ‘ + CHAR(9) + ‘, ” ”]%’
    ,@length = ISNULL(DATALENGTH(@String), 0)
    ;

    SELECT @charIndex = PATINDEX(@search_pattern, @String);

    IF (@charIndex = 0) SELECT @String = ”;

    IF (@length > 0)
    BEGIN

    SELECT
    @String = SUBSTRING(@String, @charIndex, @length);

    SELECT
    @String = REVERSE(@String);

    SELECT
    @String = REVERSE(SUBSTRING(@String, PATINDEX(@search_pattern, @String), DATALENGTH(@String)));
    END
    –IF (LEN(@String) > 0)

    IF (@NullIfEmpty = 1) SELECT @String = NULLIF(@String, ”);

    RETURN @String;

    END

    Comment by rob c — March 31, 2008 @ 7:05 PM | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: