SQL SERVER – Search Text Field – CHARINDEX vs PATINDEX

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

, , , ,
Previous Post
SQL SERVER – DBCC Commands Introduced in SQL Server 2005
Next Post
SQL SERVER – 2005 Reserved Keywords

Related Posts

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 ‘)

    Reply
  • Hi,
    This is dharmendra here.
    I want to ask a question as if:-

    “How to find the column values with special characters?”

    Reply
  • 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?

    Reply
  • Thanks..
    All ur posts are very useful for me..

    Reply
  • 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.

    Reply
  • anybody there to help me please?

    Reply
    • declare @a varchar(100) = ‘abcd#bcde#!bcde@@abcd##abcd’
      select (len( @a) – len(REPLACE(@a, ‘abcd’, ”)))/LEN(‘abcd’)

      Reply
  • 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.

    Reply
  • 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.

    Reply
  • 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

    Reply
  • 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..

    Reply
  • Nihar Kulkarni
    July 17, 2015 3:11 pm

    @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 ?

    Reply
    • 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)

      Reply
  • Nihar Kulkarni
    July 17, 2015 3:14 pm

    @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?

    Reply

Leave a Reply

Menu