How to find first/last occurrence of any character/ word in the string is one of the most frequent problems that we usually come across whenever we are dealing with the string manipulation in SQL Server.
Given below are the solutions :
- Find First occurrence of any character/ 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 @Search_String AS VARCHAR(100) SET @String ='The SQL SERVER is one of the best applications of Microsoft' SET @Search_String='the' --Find First occurrence of any character/word in the string SELECT CHARINDEX(@Search_String,@String) As [First occurrence] --OUTPUT
First occurrence
—————-
1
- Find Last occurrence of any character/ 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 @Search_String AS VARCHAR(100) SET @String ='The SQL SERVER is one of the best applications of Microsoft' SET @Search_String='the' --Find Last occurrence of any character/word in the string SELECT DATALENGTH(@String)-CHARINDEX(REVERSE(@Search_String) ,REVERSE(@String))-1 As [Last occurrence]
Last occurrence
—————
26
SQL Server Portal 