Solved

parse values with space in between

Posted on 2012-03-29
12
302 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
  • 5
  • 4
  • 3
12 Comments
 
LVL 59

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 59

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

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:ScottPletcher
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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 7

Author Comment

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

Expert Comment

by:ScottPletcher
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:ScottPletcher
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:ScottPletcher
ID: 37784211
I understand.

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

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

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 …
I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

707 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now