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.