Posts
33
Comments
186
Trackbacks
0
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
Comments
Gravatar
# re: How to find the occurrence of particular character in string - CHARINDEX
Tina
10/9/2014 7:42 PM
In your example, the second occurrence will give the exact same value as the first. For second occurrence, it should be :
CHARINDEX('a', fname, (CHARINDEX('a', fname)+1)) a_Second_occurrence
Gravatar
# re: How to find the occurrence of particular character in string - CHARINDEX
nabarun
6/22/2015 6:47 AM
what if the character to be searched is a single quote....How to find it or write it..simply putting pair of single quotes around it does not solves the problem
Gravatar
# re: How to find the occurrence of particular character in string - CHARINDEX
muni
8/9/2015 5:07 PM
i want 3 position
Gravatar
# re: How to find the occurrence of particular character in string - CHARINDEX
Sean
4/5/2017 11:48 AM
How would I get all positions if I had five /'s so I am basically looking for the substrind from pos 1 to 5th forward slash

Post Comment

Title *
Name *
Email
Comment *  
Verification