« Extract, Transform and Load (ETL) - Part 2
Masa terlalu pantas berlalu…. »

MS-SQL : CHARINDEX Vs PATINDEX

CHARINDEX and PATINDEX functions return the starting position of a pattern you specify. PATINDEX can use wildcard characters while CHARINDEX cannot.

Imagine you have a BoxNo field with below values in your table.

BoxNo
——
BOX1
BOX3
BOX4
BOX30
BOX123

Using MS-SQL syntax,You will have a few options to separate those values into number.

1.)

select BoxNo,substring(BoxNo,CHARINDEX(’X',BoxNo)+1,len(BoxNo)) as No from #temp

2.)

select BoxNo,replace(BoxNo,’BOX’,”) as No from #temp

You will have a result like below:

BoxNo No
—— ——
BOX1 1
BOX3 3
BOX4 4
BOX30 30
BOX123 123

Now, again, imagine your BoxNo having values like below. The above SQL is not valid anymore since the pattern is different already.(These are my imaginary data, real data will not have something like below. :))

BoxNo
———
BB12
Box1222
MyBox35
My13
MyBoXx123

This is the time where we need to use PATINDEX instead of CHARINDEX. Your query will look like more complex already.But with same expected result of course.

Using below SQL,

select BoxNo,substring(BoxNo,PATINDEX(’[A-Z,a-z]%’,BoxNo)+(PATINDEX(’%[0-9]%’,BoxNo)-1),len(BoxNo)) as No from #temp

Will produce something like below.

BoxNo        No
-----       -----
BB12          12
Box1222     1222
MyBox35       35
My13          13
MyBoXx123    123

In real live,you will have different type of data with different pattern…this is the time where reqular expression will be your life saver.

Till then, happy SQL’ING :)

[Ask] [Bloglines] [del.icio.us] [Digg] [Furl] [StumbleUpon] [Technorati] More »

If you're new here, you may want to subscribe to my RSS feed or get my latest post directly in your mailbox. Thanks for visiting !

Related Post:
  • SQL Injection Attack using T-SQL and HEXADECIMAL



    • Can't find what you are looking for? Go Gooogle...
    Google

    3 Responses to “MS-SQL : CHARINDEX Vs PATINDEX”

    1. oryzana on July 4th, 2008 at 6:20 pm Using Internet Explorer Internet Explorer 6.0 on Windows Windows XP

      using this to solve sql injection problem for the replacement script.. actually it’s ur script.. hehehe :) ..

    2. noris on July 4th, 2008 at 6:31 pm Using Mozilla Firefox Mozilla Firefox 3.0 on Windows Windows XP

      thanks for dropping by.

      all these seems gibberish to me.
      owh ya, imma mysql user. not really familiar with mssql syntax.
      hehehe.

      sql injection, cool topic to tackle.

    3. arejae on July 4th, 2008 at 6:54 pm Using Internet Explorer Internet Explorer 7.0 on Windows Windows XP

      @oryzana: so mmg life saver lar yer.ehehe.:)

      @noris: I am mssql user by day,mysql user by night and other database by request. :) I’m thinking to post about SQL Injection that kitaorg kena.yep..sudah kena HACK daa….now tengah kelam kabut to get all the thing back to normal.

    Leave a Reply

    AWSOM Powered