Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Retrieve second of three values separated by spaces Expand / Collapse
Author
Message
Posted Monday, December 17, 2012 4:41 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, March 21, 2014 4:50 PM
Points: 39, Visits: 274
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"
Post #1397474
Posted Monday, December 17, 2012 5:06 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, March 21, 2014 4:50 PM
Points: 39, Visits: 274
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"
Post #1397478
Posted Monday, December 17, 2012 5:49 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: 2 days ago @ 2:02 PM
Points: 500, Visits: 2,291
Using Jeff Moden's splitter 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



-- Alan Burstein



Read this article for best practices on asking questions.
Need to split a string? Try this (Jeff Moden)
Need a pattern-based string spitter? Try this (Dwain Camps)
My blog
Post #1397484
Posted Monday, December 17, 2012 6:40 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: 2 days ago @ 6:03 PM
Points: 3,590, Visits: 5,098
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!

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?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1397495
Posted Tuesday, December 18, 2012 1:12 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: 2 days ago @ 2:02 PM
Points: 500, Visits: 2,291
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.


-- Alan Burstein



Read this article for best practices on asking questions.
Need to split a string? Try this (Jeff Moden)
Need a pattern-based string spitter? Try this (Dwain Camps)
My blog
Post #1397592
Posted Wednesday, December 19, 2012 4:11 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, October 31, 2013 3:44 AM
Points: 314, Visits: 4,128
This maybe:

SELECT PARSENAME(REPLACE('HEYE-B RFE-IPA 70940',' ','.'),2);
Post #1398288
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse