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

# re: Find Position of a particular string in SQL Server
Requesting Gravatar...
Below script can also be used to separate the delimiter from a set of string and store each string in another variable.


DECLARE @ctr INT, @pos INT, @len INT,@Gap INT,@InputString VARCHAR(1000),@String Varchar(50)
SET @ctr = 1
SET @pos = 0
SET @InputString='Enter-Your-delimited-string'
SET @len = LEN(@InputString)
WHILE @pos <@len
BEGIN
SET @pos = CHARINDEX('-', @InputString, @pos)
IF @pos=0
BEGIN
SET @pos=@len
END
ELSE
SET @Gap=@pos-@ctr
SET @String = SUBSTRING(@InputString,@ctr,@Gap)
Print @String
SET @ctr=@pos+1
SET @pos = @pos+1

END

Left by harsh on Aug 06, 2014 5:07 AM

Your comment:
 (will show your gravatar)
 


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