To find a position of particular string/Char in the field of table, it is done using patindex. It might be well known for most of them. But what i found in that may be known to only few people. I want to share with everyone.
Table: tblNames
SELECT Name FROM tblNames
Name
Nagendra
John
Edward
3 row(s) affected.
Example 1: To find Char or String, here is the way. ( Many of them know this )
SELECT PATINDEX('John', name) as Pos, Name FROM tblNames
Pos Name
0 Nagendra
1 John
0 Edward
3 row(s) affected.
Example 2: To find particular Char or String in between the word. for eg., you need to look 'ohn' exists in any of the row. Then the above example will not work. it will throw the position as 0. It can be done using the wild card symbol '%'
SELECT PATINDEX('ohn', name) as Pos,PATINDEX('%ohn%', name) as PosNew, Name FROM tblNames
Pos PosNew Name
0 0 Nagendra
0 2 John
0 0 Edward
3 row(s) affected.
From the above example, it returned the position as 2(
PosNew) with the wild card symbol example. But in the first one, it returned 0(
Pos).
In simple, Behaviour of
PATINDEX will be same as
LIKE , where
LIKE is used in Where clause and
PATINDEX is used for search of a string/Char.
This can be used for search of a character or specific word according to your requirement.