parse values with space in between

I have values like this US993M1 7.5 D

Notice that there's always a space between the the first, second and third values

I could also have values like xyx 7 W

So I cant go by the length, I need to go by the space. How can I do this?
LVL 8
CamilliaAsked:
Who is Participating?
 
Kevin CrossChief Technology OfficerCommented:
You can pivot back, but try this:

SELECT FirstPart
     , SecondPart =  LEFT(WhatsLeft, CHARINDEX(' ', WhatsLeft)-1)
     , ThirdPart = RIGHT(WhatsLeft, CHARINDEX(' ', REVERSE(WhatsLeft))-1)
FROM (

SELECT FirstPart = LEFT(your_column_name, CHARINDEX(' ', your_column_name)-1)
     , WhatsLeft = SUBSTRING(your_column_name, CHARINDEX(' ', your_column_name)+1, 8000)
FROM (
    SELECT your_column_name = 'US993M1 7.5 D' UNION
    SELECT 'xyx 7 W'
) your_table_name

) derived
;
0
 
Kevin CrossChief Technology OfficerCommented:
You can use CHARINDEX(' ', your_column_name).

EDIT:
I was suggesting something like this.
SELECT FirstPart = LEFT(your_column_name, CHARINDEX(' ', your_column_name)-1)
FROM (
    SELECT your_column_name = 'US993M1 7.5 D' UNION
    SELECT 'xyx 7 W'
) your_table_name
;

However, I see you want all three parts; therefore, I would recommend use split function with ' ' as delimiter OR XML tricks if your data is conducive to conversion to XML.

Here is an example question with dbo.split() function code and usage:
http://www.experts-exchange.com/Q_23786715.html
0
 
CamilliaAuthor Commented:
i dont want XML. Let me look at split
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Kevin CrossChief Technology OfficerCommented:
That is what I suspected, so load up one of the dbo.split() functions and then call it using CROSS APPLY.
0
 
CamilliaAuthor Commented:
not sure what u mean by Cross apply. Right now, i get 3 rows back. Can I get 3 coulmns back?

So, if i pass "xyz 11 D"....i want 3 columns...but i get the values in 3 rows.
0
 
Scott PletcherSenior DBACommented:
This should do it:

SELECT
    ...
    ,CASE WHEN column_name LIKE '% %'
         THEN LEFT(column_name, CHARINDEX(' ', column_name) - 1)
         ELSE column_name END AS First_Value
    ,CASE WHEN column_name LIKE '% % %'
         THEN SUBSTRING(column_name, CHARINDEX(' ', column_name) + 1,
             CHARINDEX(' ', column_name, CHARINDEX(' ', column_name) + 1) - CHARINDEX(' ', column_name))
         WHEN column_name LIKE '% %'
         THEN SUBSTRING(column_name, CHARINDEX(' ', column_name) + 1, 500)
         ELSE '' END AS Second_Value
    ,CASE WHEN column_name LIKE '% % %'
         THEN RIGHT(column_name, CHARINDEX(' ', REVERSE(column_name)) - 1)
         ELSE '' END AS Third_Value
FROM dbo.table_name
...



For example:

SELECT
    column_name,
    CASE WHEN column_name LIKE '% %'
         THEN LEFT(column_name, CHARINDEX(' ', column_name) - 1)
         ELSE column_name END AS First_Value
    ,CASE WHEN column_name LIKE '% % %'
         THEN SUBSTRING(column_name, CHARINDEX(' ', column_name) + 1,
             CHARINDEX(' ', column_name, CHARINDEX(' ', column_name) + 1) - CHARINDEX(' ', column_name))
         WHEN column_name LIKE '% %'
         THEN SUBSTRING(column_name, CHARINDEX(' ', column_name) + 1, 500)
         ELSE '' END AS Second_Value
    ,CASE WHEN column_name LIKE '% % %'
         THEN RIGHT(column_name, CHARINDEX(' ', REVERSE(column_name)) - 1)
         ELSE '' END AS Third_Value    
FROM (
    SELECT 'US993M1 7.5 D' AS column_name UNION ALL
    SELECT 'xyx 7 W' UNION ALL
    SELECT 'test1valueonly' UNION ALL
    SELECT 'test2valuesonly test1'
) AS inline_data
0
 
CamilliaAuthor Commented:
scott, just saw your response
0
 
Scott PletcherSenior DBACommented:
Hope you don't have any rows with only one or two values -- you'll get errors from that code if/when you do.

And you have to repeat all columns through an extra level of query to get the values :-) .
0
 
CamilliaAuthor Commented:
thanks
0
 
Scott PletcherSenior DBACommented:
??

What I meant was, when you use the code you selected as "the answer", you will get errors if the column has only one or two values instead of all three.

And with that code, you have to use an extra level in every query you write to split out the three columns.

Unfortunately I can't edit out my code after you posted the next comment so if you want to use it and give all the pts to someone else I can't stop that.

Good luck on future qs.
0
 
CamilliaAuthor Commented:
i gave the credit before i saw your response.
0
 
Scott PletcherSenior DBACommented:
I understand.

Hopefully all your rows have all three values :-) .
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.