Separate two Values

  • Hello Everyone

    I am working with some odd data in the fact that someone placed two values in a single column, but not for every row. Cute huh!?!?!

    There is always a blank space between the two values in the column, if there are two values. I am having difficulty in updating only the rows with two values. I do not want the value with a single value touched, so to speak.

    This is a sample of what the data looks like:

    DECLARE @OddRows TABLE

    (

    Oddvalue VARCHAR(50)

    )

    INSERT INTO @OddRows

    SELECT '002849617 00009' UNION ALL

    SELECT '002960330 00009' UNION ALL

    SELECT '003181242 00009' UNION ALL

    SELECT '003701140 00009' UNION ALL

    SELECT '003789064 00009' UNION ALL

    SELECT '003794168 00009' UNION ALL

    SELECT '2253436' UNION ALL

    SELECT '2973536' UNION ALL

    SELECT '3353599' UNION ALL

    SELECT '3511183' UNION ALL

    SELECT '3849290' UNION ALL

    SELECT '4322571'

    SELECT * FROM @OddRows

    As you can see, some of the data in the column is what I consider a single value. Those rows are fine, as I need only that value. I need them just as they are. The other rows that have two values per row, I need only the first value.

    Even though the perfect rows have two leading zeros, I can remove the leading zeros at the next step. I have code to do that.

    This is what I need the data in the column to look like.

    DECLARE @PerfectRows TABLE

    (

    Oddvalue VARCHAR(50)

    )

    INSERT INTO @PerfectRows

    SELECT '002849617' UNION ALL

    SELECT '002960330' UNION ALL

    SELECT '003181242' UNION ALL

    SELECT '003701140' UNION ALL

    SELECT '003789064' UNION ALL

    SELECT '003794168' UNION ALL

    SELECT '2253436' UNION ALL

    SELECT '2973536' UNION ALL

    SELECT '3353599' UNION ALL

    SELECT '3511183' UNION ALL

    SELECT '3849290' UNION ALL

    SELECT '4322571'

    SELECT * FROM @PerfectRows

    Thank you in advance for all your assistance, suggestions and comments

    Andrew SQLDBA

  • Try below code

    DECLARE @OddRows TABLE

    (

    Oddvalue VARCHAR(50)

    )

    INSERT INTO @OddRows

    SELECT '002849617 00009' UNION ALL

    SELECT '002960330 00009' UNION ALL

    SELECT '003181242 00009' UNION ALL

    SELECT '003701140 00009' UNION ALL

    SELECT '003789064 00009' UNION ALL

    SELECT '003794168 00009' UNION ALL

    SELECT '2253436' UNION ALL

    SELECT '2973536' UNION ALL

    SELECT '3353599' UNION ALL

    SELECT '3511183' UNION ALL

    SELECT '3849290' UNION ALL

    SELECT '4322571'

    SELECT case when charindex(' ', Oddvalue, 1) > 0 then substring(Oddvalue, 1, charindex(' ', Oddvalue, 1)) else Oddvalue end FROM @OddRows

  • Thank you

    That worked perfectly

    I was mistaken, I do not have code to remove the leading zeros. Can you also assist with that part?

    Thanks

    Andrew SQLDBA

  • Without the CASE statement

    DECLARE @OddRows TABLE

    (

    Oddvalue VARCHAR(50)

    )

    DECLARE @PerfectRows TABLE

    (

    Oddvalue VARCHAR(50)

    )

    INSERT INTO @OddRows

    SELECT '002849617 00009' UNION ALL

    SELECT '002960330 00009' UNION ALL

    SELECT '003181242 00009' UNION ALL

    SELECT '003701140 00009' UNION ALL

    SELECT '003789064 00009' UNION ALL

    SELECT '003794168 00009' UNION ALL

    SELECT '2253436' UNION ALL

    SELECT '2973536' UNION ALL

    SELECT '3353599' UNION ALL

    SELECT '3511183' UNION ALL

    SELECT '3849290' UNION ALL

    SELECT '4322571'

    INSERT INTO @PerfectRows

    SELECT SUBSTRING(Oddvalue,1,charindex(' ',Oddvalue+' ',1))

    FROM @OddRows

    SELECT * FROM @PerfectRows

  • If the data is always numbers then just convert the data-type to integer which will remove the leading zero's.

    SELECT CONVERT(INT,SUBSTRING(Oddvalue,1,charindex(' ',Oddvalue+' ',1)))

    FROM @OddRows

  • Sowbhari (2/4/2014)


    If the data is always numbers then just convert the data-type to integer which will remove the leading zero's.

    SELECT CONVERT(INT,SUBSTRING(Oddvalue,1,charindex(' ',Oddvalue+' ',1)))

    FROM @OddRows

    Just when you're convinced the first value is always integer, you're gonna find that they're not.

    INSERT INTO @PerfectRows

    SELECT STUFF(LEFT(Oddvalue, CHARINDEX(' ' ,Oddvalue+' ')-1), 1, PATINDEX('%[^0]%', OddValue)-1, '')

    FROM @OddRows;

    SELECT * FROM @PerfectRows


    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    My thought question: Have you ever been told that your query runs too fast?

    My advice:
    INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
    The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

    Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
    Since random numbers are too important to be left to chance, let's generate some![/url]
    Learn to understand recursive CTEs by example.[/url]
    [url url=http://www.sqlservercentral.com/articles/St

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply