Retrieve second of three values separated by spaces

Question

Retrieve second of three values separated by spaces

SqlHomebrewer

SSC Eights!

Points: 874
More actions
December 17, 2012 at 4:41 pm

#393808

My brain isn't working right now. I have data such as 'HEYE-B Euro-IPA 69793' and 'HEYE-B RFE-IPA 70940'. I need to retrieve the middle value, such as 'Euro-IPA' or 'RFE-IPA'. How would that be coded to retrieve those values?
"Nicholas"

Viewing 6 posts - 1 through 5 (of 5 total)

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

SqlHomebrewer SSC Eights! Points: 874 More actions · Answer 1

5280_Lifestyle (12/17/2012)
My brain isn't working right now. I have data such as 'HEYE-B Euro-IPA 69793' and 'HEYE-B RFE-IPA 70940'. I need to retrieve the middle value, such as 'Euro-IPA' or 'RFE-IPA'. How would that be coded to retrieve those values?

Forcing my brain to work resulted in the following. It's the best that I could manage. Seems to have done the trick.

CASE WHEN (CHARINDEX(' ',ColName)=0)

THEN 'Unknown'

ELSE SUBSTRING(ColName,CHARINDEX(' ',ColName)+1,

LEN(ColName) -

LEN(LEFT(ColName, CHARINDEX(' ',ColName))) -

LEN(RIGHT(ColName, CHARINDEX(' ',ColName) - 1)))

END AS [Middle_Value]

"Nicholas"

Alan Burstein SSC Guru Points: 61136 More actions · Answer 2

Using Jeff Moden's splitter[/url] you can do this...

DECLARE @spitme TABLE (Item varchar(40));

INSERT @spitme VALUES ('HEYE-B Euro-IPA 69793'), ('HEYE-B RFE-IPA 70940');

SELECT s.Item

FROM @spitme val

CROSS APPLY test.[dbo].[DelimitedSplit8K](val.Item,' ') s

WHERE ItemNumber = 2

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

Dwain Camps SSC Guru Points: 86908 More actions · Answer 3

Another way:

DECLARE @spitme TABLE (Item varchar(40));

INSERT @spitme VALUES ('HEYE-B Euro-IPA 69793'), ('HEYE-B RFE-IPA 70940');

SELECT STUFF(STUFF(Item, 1, CHARINDEX(' ', Item), ''), CHARINDEX(' ', STUFF(Item, 1, CHARINDEX(' ', Item), '')), LEN(Item), '')

FROM @spitme

Please excuse me for having a bit of fun this morning. 😛

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
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

Alan Burstein SSC Guru Points: 61136 More actions · Answer 4

Another way:

DECLARE @spitme TABLE (Item varchar(40));

INSERT @spitme VALUES ('HEYE-B Euro-IPA 69793'), ('HEYE-B RFE-IPA 70940');

SELECT SUBSTRING(Item, CHARINDEX(' ',Item)+1, LEN(Item)-(CHARINDEX(' ',Item)+CHARINDEX(' ',REVERSE(Item))))

FROM @spitme

Fun.

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

OTF SSCertifiable Points: 6054 More actions · Answer 5

This maybe:

SELECT PARSENAME(REPLACE('HEYE-B RFE-IPA 70940',' ','.'),2);