Solved

parse values with space in between

Posted on 2012-03-29
12
309 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQl server restarts itself 6 41
Grid querry results 41 80
CREATE DATABASE ENCRYPTION KEY 1 72
Sql Server group by 10 44
by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
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…

856 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