Home > SQL Server, T-SQL > SQL SERVER – String Search using CHARINDEX(T-SQL)

SQL SERVER – String Search using CHARINDEX(T-SQL)


CharIndex() is a transact SQL function. It helps us to search for a character expression in another character expression.

Syntax:

CHARINDEX ( Character expression to Search , character expression [ , start_location ] )

or

CHARINDEX (Character expression to Search , character expression [COLLATE] [ , start_location ] )

Collate can be used to compare character expression explicitly using any collation setting. For instance, compare search string with case sensitivity in English or French language.

Points to remember

1. The return value of this function is int or bigint. When the character expression is varchar(max) or nvarchar(max) or varbinary(max) data type, then the result is bigint. otherwise, int.

2. If the character expression is null then the result is null.

3. This function returns 0 if the search character expression is not found in character expression.

4.  The size of the character expression to search must be less than or equal to 8000 characters.

Experiment

1. select CHARINDEX(‘Apple’, ‘This is an Apple’)

Output is 12. Because, the starting position of the string ‘Apple’ is 12

2. select CHARINDEX(‘Apple’, ‘Apple is my favorite fruit’, 3)

Output is o (Not found). Because, the search position starts from 3rd position of the character expression. So, it could not find the search expression in the character expression.

2.1 select CHARINDEX(‘Apple’, NULL) ; select CHARINDEX(Null, ‘Apple’);

Output is NULL. Because, the character expression is null.

3. SELECT CHARINDEX ( ‘TEST’, ‘I am testing my TSQL code’ COLLATE Latin1_General_CS_AS);

Output is o (Not found). Because, we use explicit collation based comparison with case sensitivity. So, ‘TEST’ is not in the character expression.

4. SELECT CHARINDEX ( ‘TEST’, ‘I am testing my TSQL code’ COLLATE Latin1_General_CI_AS);

Output is 6. Collation setting given will compare character expressions with case insensitivity.

5. SELECT CHARINDEX ( ‘langue’, ‘Le paramètre de langue est passé à Français.’
COLLATE French_100_CS_AI);

Output is 17. We are comparing the character expression with French collation.

6. SELECT CHARINDEX ( ‘langue’, ‘Le paramètre de langue est passé à Français.’
COLLATE French_100_CS_AI, 10);

Output is 17. In this example we have given collation and search starting position in the function argument.

What next?

I need a T-SQL statement that should give me the count of a search character expression appeared in the character expression using CHARINDEX() function.

Example

Sample Character expression is as given below.

‘NoSQL database management systems are useful when working with a huge quantity of data when the data’s nature does not require a relational model’

Character Expression to Search is ‘Data’

Output should be 3.

Please share your solution in the comment section.

Thanks for reading.

Advertisements
  1. ABC
    April 10, 2013 at 11:56 pm

    What about CHARINDEX(‘class=”abc”‘, ‘My class=”abc” ‘) ?

    • April 11, 2013 at 12:05 am

      4

    • Hemant
      June 4, 2013 at 8:41 am

      Hello,
      I want to find the character position ‘,’ using CHARINDEX function it is giving me incorrect output for the last three SQLs. Please share your though why it is not giving correct output.
      SELECT CHARINDEX(‘,’,’aaa,a,a,a,’,1)–Output 4
      SELECT CHARINDEX(‘,’,’a,a,a,a,a,a,’,2)–Output 2
      SELECT CHARINDEX(‘,’,’a,a,a,a,a,a,’,3)–Output 4 instead of 6
      SELECT CHARINDEX(‘,’,’a,a,a,a,a,a,’,4)–Output 4 instead of 8
      SELECT CHARINDEX(‘,’,’a,a,a,a,a,a,’,5)–Output 6 instead of 10

      Regards,
      Hemant

      • June 4, 2013 at 11:59 am

        Hi Hemant

        These output values are valid because you are giving search start location.

  2. Ref
    August 14, 2013 at 10:11 am

    This does not work if you are extracting a string from a column. ie, select CHARINDEX(‘favorite’, FrutiName, 5) with FruitName being a colum name. It does not take into account the starting point. Any reasonw why this could be the case

  3. August 21, 2013 at 10:14 am

    Can you please share the sample data that you have used with output?

  4. Geoff
    May 7, 2014 at 7:24 am

    select len(‘NoSQL database management systems are useful when working with a huge quantity of data when the data`s nature does not require a relational model’)-len(replace(‘NoSQL database management systems are useful when working with a huge quantity of data when the data`s nature does not require a relational model’,’data’,’dat’))

  5. poonam
    June 16, 2014 at 7:17 am

    Can you please give solution for the above example question?

  1. October 21, 2013 at 1:56 pm

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

%d bloggers like this: