We can use either CHARINDEX or PATINDEX to search in TEXT field in SQL SERVER. The CHARINDEX and PATINDEX functions return the starting position of a pattern you specify.
Both functions take two arguments. With PATINDEX, you must include percent signs before and after the pattern, unless you are looking for the pattern as the first (omit the first %) or last (omit the last %) characters in a column. For CHARINDEX, the pattern cannot include wildcard characters. The second argument is a character expression, usually a column name, in which Adaptive Server searches for the specified pattern.
Example of CHARINDEX:
USE AdventureWorks;
GO
SELECT CHARINDEX('ensure', DocumentSummary)
FROM Production.Document
WHERE DocumentID = 3;
GO
Examples of PATINDEX:
USE AdventureWorks;
GO
SELECT PATINDEX('%ensure%',DocumentSummary)
FROM Production.Document
WHERE DocumentID = 3;
GO
Summary:
PATINDEX is CHARINDEX + WildCard Search. Use either of them depending your need.
Reference : Pinal Dave (https://blog.sqlauthority.com) ,BOL – PATINDEX, BOL – CHARINDEX
57 Comments. Leave new
Hi All
PatIndex = CHARINDEX + Wild card characters
WildCard characters are :
%
_
[]
Example For PATINDEX:
SELECT CHARINDEX(‘kan’, ‘Hi Friends this is srikanth from suvara technosoft ‘)
SELECT PATINDEX(‘%___ka%h%’, ‘Hi Friends this is srikanth from suvara technosoft ‘)
Hi,
This is dharmendra here.
I want to ask a question as if:-
“How to find the column values with special characters?”
where col like [%~!#$%^&*()_+%]
I’ve been trying to get an amount value out of an ntext field based on this info. I can’t get it to work.
The field can have values like
‘1. text here € 100.000 text here’
normally amount will start with €. How can i extract € 100.000 or just 100.000 from the field?
Refer this post
Thanks..
All ur posts are very useful for me..
Hi
i need one efficient query to find keyword appearance count from one table
for an example i have 3000 standard values in one table named as table A.and 7 crore records in another table named as B.
Table A data sample is
1.abcd
2.bcde
3.efgh
etc
Table B sample data
ID column B
123 abcd#!#bcde#!#efgh
234 efgh#!#bcde#!#abcd
etc
so i need to find the apcd appearance count from table B.
Hope we can do it with like statement.but thats not efficient with large amount of data.can any one please specify the efficient way to find the appearance count of table A values in table B.
anybody there to help me please?
declare @a varchar(100) = ‘abcd#bcde#!bcde@@abcd##abcd’
select (len( @a) – len(REPLACE(@a, ‘abcd’, ”)))/LEN(‘abcd’)
Hi Pinal, Just wanted to note that charindex can actually take upto three arguments, the third one being the optional argument and is the starting position to start the search.
Hi Pinal, I have a requirement like
table -A having a column with keywords separated by comma(,),
table – B also having another column like keywords separated by comma,
now I need a query to get rows from table-A with any word matching in both tables
can you look into it and get me some solution.
Hi,
The values in table a are ‘hello%world’ , ‘hello world’, ‘hello’,’world’.
Now i need to search a value which has a %.
Can you pls help me out in this
where col like ‘%[%]%’
I am trying to correct a sync issue on a project I have been assigned to. The issue lays in this line.
PATINDEX(‘%’+(CONVERT(varchar(64),’BF935C02-1319-41FA-BCDF-09CC4F517F2A’))+’%’,O.HIERARCHY)>0
The GUID is the Parent OrganizationID of 4 other Organizations that make up Regions. These regions have several organizations under them…I need to add these four regions to my SYNC job to suck in my new info…
If I move any Organization from a region and place it under the Blanket GUID from the statement above, all the new information will come through…
any help would be great..
@Pinal Sir , I am trying to optimize query … please find details which we tried
declare @MyString nvarchar(1000) ='[{4}/{4}+({4}+{198})-{164}]’
Expected Output – {4},{4},{4},{198},{164} (We fetched output also , but using lot much query of replace n all )
Following are some tryouts ,but no success-
1) select REPLACE(@MyString, SUBSTRING(@MyString, PATINDEX(‘%[^.[]+()/-]%’, @MyString), 1), ‘,’)
2) select STUFF(@MyString,patindex(‘%[^.[]+()/-]%’,@MyString),len(‘+’),’,’)
How we will replace special characters using patindex ?
DECLARE @str VARCHAR(400)
–Add your specialcharacters here
DECLARE @specialchars VARCHAR(50) = ‘%[~,@,#,$,%,&,*,/, ,+,(,),.,!^?:]%’
SET @str = ‘[{4}/{4}+({4}+{198})-{164}]’
WHILE PATINDEX( @specialchars, @str ) > 0
—Remove special characters using Replace function
SET @str = Replace(REPLACE( @str, SUBSTRING( @str, PATINDEX( @specialchars, @str ), 1 ),”),’-‘,’ ‘)
select replace(@str,’}{‘,’},{‘)
SELECT substring(replace(@str,’}{‘,’},{‘),2,len(replace(@str,’}{‘,’},{‘))-2)
@pinal Sir , we are trying query optimization for our task .Before we fetched output using few lines of code. But we are trying to optimize it.
declare @MyString nvarchar(1000) ='[{4}/{4}+({4}+{198})-{164}]’
Expected Output – {4},{4},{4},{198},{164}
————————————
Following are some try out , but no success .
1) select REPLACE(@MyString, SUBSTRING(@MyString, PATINDEX(‘%[^.[]+()/-]%’, @MyString), 1), ‘,’)
2) select STUFF(@MyString,patindex(‘%[^.[]+()/-]%’,@MyString),len(‘+’),’,’)
Can you guide us on the same using patindex?