Posts
33
Comments
186
Trackbacks
0
Find the occurrence of word/character in SQL column with wildcard character - PATINDEX

CharIndex and PatIndex both can be used to determine the presence of character or string within sql column data. Both returns the starting position of the first occurrence of the character/word within expression.

However, one major difference between CharIndex and PatIndex is that later allows the use of wild card characters while searching for character or word within column data.

Also, Patindex is useful for searching within Text datatype.

Allowed wild card characters are % and _ .

" % "  - use it for any number of characters

" _ "  - use it for a single character.

Syntax

PATINDEX('%pattern%', string_expression)

Note - it's mandatory to include pattern within %% characters.

  • returns starting position of occurrence of pattern, if found.
  • returns 0, if not found
  • returns NULL , if either pattern or string_expression is null.

Example

  1. SELECT fldname FROM tblUsers WHERE PatIndex('%v_pin%', fldname) > 0

 

posted on Thursday, March 25, 2010 5:35 PM Print
Comments
Gravatar
# re: Find the occurrence of word/character in SQL column with wildcard character - PATINDEX
Vipin
9/28/2010 3:24 PM
PAtIndex and CharIndex are used to find whether the matched pattern exists in the string.

If you need to count the number of occurrences of word in column, unfortunately there is no straight-forward function available to do that & you'd have to use workarounds like REPLACE
Gravatar
# re: Find the occurrence of word/character in SQL column with wildcard character - PATINDEX
Romatoit Artrit
2/22/2011 4:09 PM
This is one of the good artical for learning wildCard. it has solved my all search related problems.
Gravatar
# re: Find the occurrence of word/character in SQL column with wildcard character - PATINDEX
breville tea maker
3/30/2011 3:04 AM
Thanks for pointing this out. Did not know about this little trick.
Gravatar
# re: Find the occurrence of word/character in SQL column with wildcard character - PATINDEX
web hosting india
5/3/2011 4:24 PM
Thank you for the code! I always have problems with SQL - it is just weird for me.

Post Comment

Title *
Name *
Email
Comment *  
Verification