Extract a string between two special characters

Question

Extract a string between two special characters

ksrikanth77

SSCrazy

Points: 2232
More actions
April 30, 2013 at 9:11 am

#277213

Hi Everyone,
Can anyone of you please help me to get the T-SQL to extract a string between two special characters.
Example: /IT/Management, /PRODUCTION/Name
I need to extract IT and PRODUCTION from the above Example. The / is always in the first position
I tried the below and It works but I wanted to use a column from a table rather than a predefined value.
select SUBSTRING('/IT/management1234',CHARINDEX('/','/IT/management1234')+1,LEN('/IT/management1234')-charindex('/',REVERSE('/IT/management1234'))-1)
Thanks in advance
Srikanth Reddy Kundur

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

Basically what you need to do here is parse your string values. There are a couple ways you can do this pretty easily. If you have consistent data like your example using PARSENAME is probably the easiest.

with myData(SomeValue) as

(

select '/IT/Management' union all

select '/PRODUCTION/Name'

)

select PARSENAME(replace(SomeValue, '/', '.'), 2) from myData

The other option is to use a full splitter. This is probably a bit of overkill for your simple example.

with myData(SomeValue) as

(

select '/IT/Management' union all

select '/PRODUCTION/Name'

)

select Item

from myData

cross apply dbo.DelimitedSplit8K(SomeValue, '/')

where ItemNumber = 2

You can find the code for the DelimitedSplit8K function by following the link in my signature about splitting strings.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

SELECT

d.Example,

[Output] = SUBSTRING(d.Example, 2,

NULLIF(CHARINDEX('/',d.Example,2),0)-2)

FROM ( -- sample data

SELECT Example = '/IT/Management' UNION ALL

SELECT '/PRODUCTION/Name' UNION ALL

SELECT 'Nothing'

) d

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

ksrikanth77 SSCrazy Points: 2232 More actions · Answer 3

Thank You for the Quick Responses

It Works for my requirement:-)