September 25, 2012 at 4:14 pm
OK, so I would like to write a query that pulls info after a certain number of underscores. For example:
I have info labeled in column1 as 'sampleinfo_sampleinfo_sampleinfo_sampleinfo'
Where ever entry has four pieces of information separated by three underscores.
I would like to write something so I can say "pull 'sampleinfo' after the second underscore" (for example)
My first thought was this...
CASE
WHEN [column1]
LIKE '%[_]%[_]sampleinfo%'
THEN 'Sample Info'
END
AS 'New Column Title'
However this requires me to have to know the name of the info after the 2nd underscore.
So then I was thinking something with substring. I got it to work but it is a bit complicated.
Select
substring ([creative name],
charindex ('_', [sampleinfo])+1,
charindex ('_', [sampleinfo],
charindex ('_', [sampleinfo])+1)
-charindex ('_', [sampleinfo])-1)
as [sampleinfo_2],
Can anyone help me simplify this? Or come up with something more simple? Thanks in advance!
September 25, 2012 at 5:23 pm
I thought I had posted this before, but here goes a second attempt.
If you are sure that there are ALWAYS four parts, you can "Cheat" and use the PARSENAME function together with REPLACE.
as in ...
declare @t table
(someStringField varchar(256))
insert @t
values
('XXX_yyy_zzz_QQQ')
,('123_456_789_0aa')
,('abc_def_ghi_jkl')
,('jhduthe_nsognfhdteigh_skweyur_ebgfvdn')
select PARSENAME(REPLACE(someStringField,'_','.'),3)
from @t
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgSeptember 26, 2012 at 5:32 pm
Unfortunately I will be working with entries that have more than four pieces of information (up to ~15) and you're right PARSENAME only works with up to 4 "part names."
Any others thoughts?
(Thanks!)
September 26, 2012 at 6:04 pm
OK Here's your answer.
It's now time to understand it .... 🙂
declare @yourTable table
(i int identity (1,1), someStringField varchar(256))
insert @yourTable
values
('XXX_yyy_zzz_QQQ_...')
,('123_456_789_0aa_..._..._......._')
,('abc_def_ghi_jkl')
,('jhduthe_nsognfhdteigh_skweyur_ebgfvdn')
SELECT
i
,targetNode
FROM
(SELECT
yt.i
,ROW_NUMBER() OVER (PARTITION BY yt.i ORDER BY N) as rowNum
,SUBSTRING('_'+yt.someStringField+'_',N+1,CHARINDEX('_','_'+yt.someStringField+'_',N+1)-N-1) AS targetNode
FROM
dbo.Tally t
CROSS JOIN @yourTable yt
WHERE
N < LEN('_'+yt.someStringField+'_')
AND SUBSTRING('_'+yt.someStringField+'_',N,1) = '_') as myBrainHurts
WHERE
rowNum = 3 ---- or which ever node you want to get to
Credit must be given to Jeff Moden for the discovery. He's got a very good explination http://www.sqlservercentral.com/articles/T-SQL/62867/.
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgSeptember 27, 2012 at 12:27 am
There's actually a much easier way (using Jason's set up data):
SELECT i, targetNode=item
FROM @yourTable
CROSS APPLY dbo.DelimitedSplit8K(someStringField, '_')
WHERE itemnumber = 3
Again, credit to Jeff Moden for the DelimitedSplit8K function: http://www.sqlservercentral.com/articles/Tally+Table/72993/
He's the man!
The DelimitedSplit8K function internally uses a Tally table, not unlike Jason's suggestion. Just saves you the typing.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply