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

SQL Function, SQL Scripts, SQL Server, SQL Server Security, SQL Utility
Previous Post
SQL SERVER – DBCC Commands Introduced in SQL Server 2005
Next Post
SQL SERVER – 2005 Reserved Keywords

Related Posts

57 Comments. Leave new

  • Alex Bransky
    June 20, 2007 3:21 pm

    CHARINDEX will only work on a text column if it can implicitly be converted to varchar (or ntext to nvarchar), so if the data is greater than 8000 bytes (approximately) you’ll get an error.

    Reply
    • Pranshul Gupta
      June 20, 2013 12:07 pm

      Please elleborate it Alex. I tried it with an entry of more than 24000 characters. Still it works fine.

      Reply
  • Hello Dave,

    I have been using PATINDEX and CHARINDEX for my project. But I want to extend the functionality so that I can search a occurence of a ‘word’ in a ‘text’ field and a ‘varchar’ field. Is there any way this can be achieved with the same functions.

    If anyone knows about how to handle the search, kindly mail me.

    Regards
    Keerthi

    Reply
    • Refer this post. This will search for a value in all character columns of all the tables and and return column name , table name

      Reply
  • Pradeep Jagadeesan
    November 26, 2007 6:20 pm

    How to search for special characters and remove it from the string
    actual string ‘Peninsula Inv00estment Management Comç’

    after removed special char

    ‘Peninsula Inv00estment Management Com’

    Pl give me a solution.

    Reply
    • declare @string varchar(100)
      declare @string1 varchar(100)

      set @string = ‘Peninsula Inv00estment Management Comçagement Comç0estment’
      set @string1 = replace(@string, char(231), ”)
      select rtrim(@string1)

      the output is
      Peninsula Inv00estment Management Comagement Com0estment

      Reply
  • You can use the REPLACE() function to remove or replace special characters.

    If you have a string like ‘Peninsula Inv00estment Management Comç’ that is in a field called CompanyName just type the following:

    Select REPLACE(CompanyName,’ç’, ”) from Company. You can nest several REPLACE() functions to take out numerous characters at once.

    Ron

    Reply
  • Hi, I’ve just started using PatIndex – it’s proved useful in a lot of my work lately.

    Right now I am trying to do a search for one string, OR another. In other words a kind of ‘string count’ function. So if either substring exists in a value, it returns true, or a number.

    The string is: where column like ‘%a%’ or column like ‘%b%’

    The only problem is that ‘column’ is itself a very long-winded subquery, so I don’t want to have to write it out twice in the above string.

    So can I use some kind of regex in patindex to find the above?

    Hope this makes sense. thanks for your ideas..

    Reply
    • Vedran Kesegic
      January 15, 2010 3:29 pm

      “The string is: where column like ‘%a%’ or column like ‘%b%’

      The only problem is that ‘column’ is itself a very long-winded subquery, so I don’t want to have to write it out twice in the above string.”

      You can move your query down to FROM clause (inline view), a then filter it with outer where clause.
      E.g. if your query is:

      select … from table
      where (big expression) like something
      or (big expression) like something

      it can be written as:

      select * from — * is ok – columns are defined in inline view
      (
      select … ,
      bige = (big expression) — just once
      from table
      ) t
      where bige like something
      or bige like something

      Reply
  • i am using PatIndex and need more clear code… for example

    i want no. of letters next to the symbol ‘-‘ so i use

    patindex(‘%-%’, company-name). its working successfully.

    but if the word is of ‘company-name-id’ and i want to

    consider the symbol which is appearing at the last of the

    word, what i have to do? patindex(‘%-%’, company-name-
    id)

    if i use this i am getting same as the previous… any one plz

    suggest me the correct solution for this?

    Reply
    • Can you post some more sample data with expected result?

      Reply
    • Did anyone ever answer your question about searching for the location of the second – ? I am also trying to do this

      Reply
      • Refer this example
        declare @s varchar(100)
        set @s=’company-name-id’
        set @s=substring(@s,patindex(‘%-%’, @s)+1,len(@s))
        set @s=substring(@s,patindex(‘%-%’, @s)+1,len(@s))
        select @s

  • Thanks! Your tips helped me clean and move data from one database to another :)

    Nothing fancy, but was fun at least :D

    Dave Coates
    Port Elizabeth .NET Community Lead
    South Africa

    Reply
  • One bit of info I’m having trouble finding in any similar articles is… if PATINDEX is simply a more powerful CHARINDEX, why bother ever using CHARINDEX? Is there a performance difference?

    Reply
    • There may a slight performance difference. Note that patidex is desinged to work in unicode data too

      Reply
  • Brian Tkatch
    July 6, 2009 7:23 pm

    @Ron

    This is a similar question as to why use ‘=’ if we can always use ‘LIKE’. The major difference is if a wildcard needs to be escaped or not.

    I would guess, but i have no idea, that pattern searching is slower because it does more.

    Reply
  • see the diff’

    @Name=Mari & Manikandan

    select CHARINDEX(‘ & ‘,@Name)

    select PATINDEX(‘%[^ ]&[^ ]%’,@Name)

    Reply
  • sorry,

    select CHARINDEX(‘&’,’Mari&Mani’)

    select PATINDEX(‘%[^ ]&[^ ]%’,’Mari&Mani’)

    Reply
  • watch this,

    select CHARINDEX(‘[^ ]&[^ ]’,’Mari&Mani’)

    select PATINDEX(‘%[^ ]&[^ ]%’,’Mari&Mani’)

    Reply
  • How do I find Number in string in Mysql.
    All i need is the position of the number.
    a number can be anything between 0-9

    In patindex we can do:

    Select patindex(‘%[0-9]%’, columname)

    But dunno how to find number in my string using any function in Mysql

    Reply
  • Hi,
    i have the column name empID in multiple tables in my database(sql server 2005)

    I have to find the total nuber of occurance of empID in the whole database (count the records (data) of all tables which contains the empID)

    how to write the query to achieve the above.

    Thanks in advance,
    Nikhil.

    Reply
    • As a suggestion, you need to work on

      SELECT t.name AS table_name,
      SCHEMA_NAME(schema_id) AS schema_name,
      c.name AS column_name
      FROM sys.tables AS t
      INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
      WHERE c.name = ’empid’
      ORDER BY schema_name, table_name;

      Reply
      • you can use following query for sql 2005:

        select * from sysobjects where id in (select id from syscolumns where name =’empid’)

    • Do you need to know number of tables that has empid or tables that has particular empid value?

      Reply
  • How about another idea? I’m also curious how would doing a CHARINDEX on a field in a record to find 2 characters perform in comparison to using dynamic sql to pull a string out of that same field to do use the IN function?

    DECLARE @sql varchar(2000)
    SELECT @sql =
    (
    SELECT
    ‘(”’ + c1 + ”’)’
    FROM
    t_control (NOLOCK)
    WHERE
    control_type = ‘OVERRIDE’
    )
    SET @sql = ‘SELECT COUNT(*) WHERE ”XX” IN ‘ + ” + @sql + ”
    SELECT @sql

    EXEC (@sql)

    Reply
    • There is a chance that dynamic sql may outperform charidex if the XX is a indexed column

      Reply
  • Charlie Chisholm
    June 30, 2010 1:30 am

    I have a text type column in one of my tables that will contain large amounts of text….25,000 to 40,000 characters. When I try to use charindex(‘fcCount’,data) where data is the column containing the text, it will always return zero, even though, when I look the data, the ‘fcCount’ is actually there. This is a SQL 2000 database that we plan to upgrade this year, but need to get this working in the interrum. Any help would be appriciated.

    Reply
    • Did you get any calues when you try this?

      select columns from table
      where data like ‘%fcCount%’

      Reply
  • Cristina Gonzalez
    July 2, 2010 9:38 pm

    Hello All,

    I have a table that contains a field where I store several items, and one of them is an IP address, enclosed in pipes.
    Example:

    DEVICE IP: |192.168.2.90| Puerto: 1117 01234567890 14142422 Resultado Positivo!

    The IP of course is different in lenght for each record; I am using this function:

    SELECT id, SUBSTRING(transaccion, CHARINDEX(‘|’, transaccion)+1,15) AS ip FROM Mydatabase.dbo.Mytable

    But it is extracting another string with a constant lenght.

    Is there a way to extract the IP, using a function letting know SQL that the string is enclosed by Pipes?

    Thank you all!

    Reply
    • This is generalised method

      select replace(parsename(replace(replace(data,’.’,’*’),’|’,’.’),2),’*’,’.’) from
      (
      select ‘DEVICE IP: |192.168.2.90| Puerto: 1117 01234567890 14142422 Resultado Positivo!’ as data
      ) as t

      You can refer these to get more indeas

      Reply
  • Hi,

    I usually get a data from our clients in SQL. I need to import it in a software which can import it only in csv format. If there is a comma in any field in SQL (by error) it makes it as a separate column while exporting. Then the software throws an error. Hence before exporting it to a csv file, I want to know, if there is any comma in any of the fields. I know the query

    where column_name like ‘%,%’

    But the problem is, there are more than 60 columns to be checked. It is very time consuming. So is there is any other simple way to search it in Database as a whole (rather than columnwise).

    Regards,
    Tejas

    Reply
  • Hi Tejas,

    You should export data to CSV with text qualifier like “. So your data looks like: “Tejas, Shah”,”AAAA”,”BBB” etc..

    So when software tries to import it, it will not consider “,” between qualifier.

    Let me know if it helps you.

    Thanks,
    Tejas Shah
    SQLYoga.com

    Reply
    • Hi Tejas,

      Thanks for your response. I will do it tomorrow and will let you know.

      Thanks,
      Tejas

      Reply
    • Hi Tejas Shah,

      Software considers only ‘Comma’. So its not working. I think, i need to check individually.
      So my option is export it to excel, Ctrl+F, check the fields where commas exist, then replace in sql. Still, its too time consuming. Thanks for ur suggestion. I can use it for some other softwares.

      Regards,
      Tejas

      Reply
  • Hi All

    I have a requirement to change a column’s value in a table carrying 16 digit credit card number(Column name is ‘SCREDITCARDNO’ and data type is varchar(50),table name is ‘ContactDetails’) into—->

    First 6 digit of credit card number, followed by ****** (6 starts) and then followed by last 4 digit of the credit card number.

    It’s SQL Srver 2005.

    Please help me with this.

    Reply
    • First do the SELECT

      SELECT left(SCREDITCARDNO,6)+’******’+right(SCREDITCARDNO,4) FROM ContactDetails

      Reply

Leave a Reply