How to find the occurrence of particular character in string - CHARINDEX

Posted by Vipin on Geeks with Blogs See other posts from Geeks with Blogs or by Vipin
Published on Tue, 23 Mar 2010 14:48:33 GMT Indexed on 2010/03/23 16:03 UTC
Read the original article Hit count: 227

Filed under:

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

 

 

© Geeks with Blogs or respective owner