Saturday, July 18, 2009

charindex Vs patindex

charindex : Returns the starting position of the first occurrence of a pattern in a specified expression ,or zero if the pattern is not matched

E.g: select charindex('+','anish+varghese')
Result : 6

patindex:Returns the starting position if found.And it can use wildcard characters

E.g: select patindex('%+%','anish+varghese')
Result : 6

Where is the difference ?
Actually patindex act as a like operator .

% : any string of zero or more characters
E.g : select charindex('%+%','anish+varghese')
Result : 0
E.g: select patindex('%+%','anish+varghese')
Result : 6
E.g :select patindex('%+','anishvarghese+')
Result :14
E.g:select patindex('+%','+anishvarghese')
Result :1

_(underScore) :any single character

E.g:select patindex('%_r%','anishvarghese')
Result :7

[ ] : Any single character with in the specified range [a-z] or ser [abcd]

E.g:select patindex('%[0-9]%','anish4varghese')
Result:6
[^] :Any sigle character not with in the specified range [^a-z] or set [^abcd]
E.g:select patindex('%h%','anish4varghese')
Result :5
E.g:select patindex('%h[^4]%','anish4varghese')
Result :11


0 comments:

 
Counter