Geeks With Blogs
Nagendra Prasad
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. Posted on Tuesday, March 31, 2009 4:24 PM | Back to top


Comments on this post: Find Position of a particular string in SQL Server

# re: Find Position of a particular string in SQL Server
Requesting Gravatar...
Very Awesome! Using with LEN and Substring can extract a given portion of text of variable length.

Thanks!
Left by Howie on Sep 02, 2009 8:55 AM

# re: Find Position of a particular string in SQL Server
Requesting Gravatar...
Its nice but not useful for me.. i want know the search query to find a string in phpmyadmin
Left by Abhishek on Jun 05, 2010 1:17 AM

# re: Find Position of a particular string in SQL
Requesting Gravatar...
Find Position of a particular string in SQL
Left by jignesh on Sep 23, 2011 8:22 PM

# re: Find Position of a particular string in SQL Server
Requesting Gravatar...
Thanks for your posting!!! :D
Left by Jaqueline on Oct 25, 2011 12:42 AM

# learn sql
Requesting Gravatar...
very nice
Left by sql tutorial on Mar 05, 2012 11:34 PM

# re: Find Position of a particular string in SQL Server
Requesting Gravatar...
Here is i/p of a table:
Name Surname unique id
kunal shekhar & Raj Anand 1
Ram & k kelly & R Singh 2
Rahan & raj & kali kumar 3

How to get o/p like this:--

Name Surname unique id
kunal shekhar 1
Raj Anand 1
Ram Singh 2
k kelly Singh 2
R Singh 2
Rahan kumar 3
raj kumar 3
kali kumar 3

Left by kunal shekhar on Nov 27, 2012 5:54 PM

Your comment:
 (will show your gravatar)
 


Copyright © nagendraprasad | Powered by: GeeksWithBlogs.net | Join free