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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

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
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.