How to find first and last occurrence of any char or word in sql query?
- Find First occurrence of any character or word in the string :
In the given below example, we need to search for the first occurrence of word ‘the’ in the sentence.
DECLARE @String AS VARCHAR(100)DECLARE @strSearch AS VARCHAR(100)SET @String ='Hi SQL SERVER is from Microsoft'SET @strSearch ='Hi'--Find First occurrence of any character or word in the stringSELECT CHARINDEX(@strSearch ,@String) As [Result]--OUTPUT |
First occurrence
___________________
1
___________________
1
- Find Last occurrence of any character or word in the string :
In the example given below, we need to search for the last occurrence of word‘the’ in the sentence.
DECLARE @String AS VARCHAR(100)DECLARE @strSearch AS VARCHAR(100)SET @String ='The SQL SERVER is from Microsoft hi'SET @strSearch ='hi'--Find Last occurrence of any character or word in the stringSELECT DATALENGTH(@String)-CHARINDEX(REVERSE(@strSearch ),REVERSE(@String))-1 As [Result] |
–OUTPUT Last occurrence
_________________________
33
_________________________
33
Arjun's Blog 