Using CharIndex To Find an Character Inside the Substring of an Expression

In this topic, we are going to discuss how to use charindex and substring  Sql functions. First of all analyze together what we need to do. Let's assume that we got a problem which is that there is a column inside our Sql table. This column contains data with nvarchar type and also is about NBA basketball teams. Data inside the column comes like that 'Cleveland-Team', 'Lakers', 'Denver', 'Chicago-Team',  etc.  What is the point here we are stuck in? We have to pick the only related part of data to a real team's name. It means to pick team name by using separated substring. Separated substring must be taken before hyphen(-) character. ('Cleveland-Team' => Cleveland)

We have 3 things to do now.
- Determine whether the word contains (-) char or not. If the hyphen doesn't exist inside the word display it or if it has you can continue with the second job.
- Determine the index of the hyphen(-) char inside the string(word).(We will use CHARINDEX of Sql Methods)
-Separate the left part of the string from the first character to the index of the hyphen(-). (We will use CHARINDEX of Sql Methods)

CHARINDEX ( expressionToFind , expressionToSearch [ , start_location ])  

It can be used to search an expression inside the another one. If the expression is found, CharIndex method is going to return its starting position. It requires an expression to find and an expression to search like below.

Sample : select CHARINDEX('-','Cleveland-Team');

Result :  10

SUBSTRING ( expression ,start , length )

It returns a part of expression from a start index to sum of start+lenght. We have an expression now like Cleveland-Team, and also have the length of the above sample. We are giving 1 as start parameter.

Sample : select SUBSTRING('Cleveland-Team',1,CHARINDEX('-','Cleveland-Team'));

Result : Cleveland-

Notice that there is the hyphen char at the end of the expression. We want to get rid of it by decreasing the length parameter.(length -1 )

Finally, I want to show you how to filter if a word contains an expression, and how to get to its pieces.

declare @word as nvarchar(100)='Cleveland-Team';

select IIF(CHARINDEX('-',@word)=0,
    @word,SUBSTRING(@word,1,CHARINDEX('-',@word)-1))

Hope this article helps you, see you later.