Only this week, get the SQL Complete Track of 9 courses in a special prize of $330 $89!
Simple text functions
How to modify Strings
20. The CHARINDEX() function


Great! Let's try another function.

There are times when you need to find a certain expression or set of characters in a string. Maybe you don't know what the entire string contains, but you know it has the letters 'br' in there somewhere. To find the index location of this string, you use the CHARINDEX() function. It has three parameters: the character string you want to find, the string or field you are searching, and the index value where your search should start. Here's an example:

SELECT CHARINDEX(N'br', N'abracadabra', 5);

The text N'abracadabra' starts with the letter 'a' (index position 1). But if you start from index 5 as we did here, the first occurrence of the string 'br' will be at index 9. The position returned by this CHARINDEX() function is indeed 9. Notice that 'br' also occurs at position 2, but since that is before the start point of 5, a 2 is not returned.


In the Item table, show the position of the letter 'r' in all item names. Skip the first two letters of each item name and start from index 3. Name the column RIndex.

Note that CHARINDEX() returns 0 if the pattern is not present in the searched string.

Stuck? Here's a hint!

Use CHARINDEX() with three parameters: substring, string, and start index: CHARINDEX(N'r', Name, 3).