How to find the occurrence of particular character in string - CHARINDEX
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 -
Syntax
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.
Example
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 |
posted on Tuesday, March 23, 2010 3:48 PM Print