Sunday, May 21, 2017

Getting the string between two repeating characters in SQL

There could be a need to get the string which is in between the repeating characters in the given parent string.

Example - "/a quick silver/fox jump over the/lazy dog"
Part 1 - a quick silver
Part 2 - fox jump over the
Part 3 - lazy dog

Problem: To get the Part1, Part 2, Part 3

DECLARE @test varchar(300)
SET  @test  = '/a quick silver/fox jump over the/lazy dog'

--Getting Part 1
SELECT SUBSTRING(@test, CHARINDEX('/', @test)+1 , CHARINDEX('/', @test, CHARINDEX('/', @test)+1) - (CHARINDEX('/', @test) + 1))

--Getting Part 2
SELECT SUBSTRING(@test, (CHARINDEX('/', @test, CHARINDEX('/', @test)+1)+1), ((CHARINDEX('/', @test, CHARINDEX('/', @test, CHARINDEX('/', @test)+1)+1))-(CHARINDEX('/', @test, CHARINDEX('/', @test)+1)+1)))

--Getting Part 3
SELECT SUBSTRING(@test, (CHARINDEX('/', @test, CHARINDEX('/', @test, CHARINDEX('/', @test)+1)+1)+1), LEN(@test))


Hope it will help my developer community.