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 (http://blog.SQLAuthority.com) ,BOL – PATINDEX, BOL – CHARINDEX

About these ads

56 thoughts on “SQL SERVER – Search Text Field – CHARINDEX vs PATINDEX

  1. 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.

  2. 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

  3. 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.

    • 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

  4. 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

  5. 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..

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

  6. 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?

  7. Pingback: SQLAuthority News - Best Articles on SQLAuthority.com Journey to SQL Authority with Pinal Dave

  8. 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?

  9. @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.

  10. 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

  11. 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.

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

  12. 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)

  13. 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.

  14. 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!

  15. 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

  16. 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

    • 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

  17. 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.

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

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

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

  20. 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?

  21. 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.

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

  22. 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.

  23. 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.

  24. 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

  25. 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..

  26. Pingback: SQL SERVER – Weekly Series – Memory Lane – #024 | SQL Server Journey with SQL Authority

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s