Solved

parse values with space in between

Posted on 2012-03-29
12
314 Views
Last Modified: 2012-06-21
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?
0
Comment
Question by:Camillia
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
  • 3
12 Comments
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 37782731
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
 
LVL 7

Author Comment

by:Camillia
ID: 37783294
i dont want XML. Let me look at split
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 37783322
That is what I suspected, so load up one of the dbo.split() functions and then call it using CROSS APPLY.
0
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
LVL 7

Author Comment

by:Camillia
ID: 37783700
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
 
LVL 60

Accepted Solution

by:
Kevin Cross earned 500 total points
ID: 37783749
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
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 37783807
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
 
LVL 7

Author Comment

by:Camillia
ID: 37783863
scott, just saw your response
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 37783885
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
 
LVL 7

Author Comment

by:Camillia
ID: 37783979
thanks
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 37784000
??

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

Author Comment

by:Camillia
ID: 37784200
i gave the credit before i saw your response.
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 37784211
I understand.

Hopefully all your rows have all three values :-) .
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question