String Function – CHARINDEX()

I am going to continue my series of very short articles or tidbits on Transaction SQL string functions. I will exploring the CHARINDEX() function today.

The CHARINDEX() function takes a expression to find, a expression to search and a optional start position. It returns the first position in which the search string was found.

The following example is based upon the television jingle that I heard as a kid for Trix cereal.

The example below contains four test cases:

  1. Word is found in target string.
  2. Word is not found in target string.
  3. Starting position > length of string.
  4. NULL expression to find.
  5. NULL expression to search.

The results of four test cases are the following:

  1. 7 – Seventh character position is start of string.
  2. 0 – Expression to find not found.
  3. 0 – Expression to find not found.
  4. NULL – Position is unknown.
  5. NULL – Position is unknown.

Next time, I will be exploring the CONCAT() function. This new function was introduction in SQL Server 2012.

Related posts

Leave a Comment