Feeds:
Posts
Comments

Posts Tagged ‘SQL SERVER – How to find first/last occurrence of any character/ word in the string’

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

Advertisements

Read Full Post »