April 30, 2013 at 9:11 am
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
April 30, 2013 at 9:22 am
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/
April 30, 2013 at 9:24 am
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
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
April 30, 2013 at 1:31 pm
Thank You for the Quick Responses
It Works for my requirement:-)
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy