CODEWITHSUNDEEP
sql-server-2008
Puskar
Puskar

Reputation: 97

Get the text between two characters in sql server

i have tried using charindex function but unable to get it.

Text is :- Offshore/BLR/IN

i want only BLR" out of this.

please help.

Upvotes: 0

Views: 9710

Answers (1)

Martin
Martin

Reputation: 16453

Presuming that what you are actually asking is how to get the text between the forward-slashes, you could do this easily using CHARINDEX.

See the following example:

DECLARE @Text varchar(100)
SET @Text = '- Offshore/BLR/IN'
PRINT CHARINDEX('/', @Text)
PRINT CHARINDEX('/', @Text, CHARINDEX('/', @Text) + 1)

This will output 11 and 15, respectively, which shows the position of the first and second forward-slash.

The second position is found by searching the text that occurs after the first position.

Following this it's just a simply case of using SUBSTRING to extract the text from the middle of the slashes:

PRINT SUBSTRING(@text, CHARINDEX('/', @text) + 1, CHARINDEX('/', @text, CHARINDEX('/', @text)+ 1)  - CHARINDEX('/', @text) - 1)

This essentially gets the text between the first slash (+1 position to exclude the slash), to the position of the second slash minus the position of the first slash minus 1 (to remove the first slash). This is basically:

PRINT SUBSTRING(@text, 11 + 1, 15 - 11 - 1)

Note that is the text does not contain both slashes then this code will fail. It contains no bounds or error checking at all, and you should look to add this.

Upvotes: 2

Related Questions