How to find the occurrence of particular character in string - CHARINDEX
- by Vipin
Many times while writing SQL, we need to find if particular character is present in the column data. SQL server possesses an in-built function to do this job -
CHARINDEX(character_to_search, string, [starting_position])
Returns the position of the first occurrence of the character in the string.
NOTE - index starts with 1. So, if character is at the starting position, this function would return 1.
Returns 0 if character is not found.
Returns 0 if 'string' is empty.
Returns NULL if string is NULL.
A working example of the function is
SELECT CHARINDEX('a', fname) a_First_occurence,
CHARINDEX('a', fname, CHARINDEX('a', fname)) a_Second_occurrence
FROM Users
WHERE fname = 'aka unknown'
OUTPUT
-------
a_First_occurence
a_Second_occurrence
1
3
