Separate two Values

• AndrewSQLDBA

  SSC-Insane

  Points: 21827

  More actions

  February 4, 2014 at 8:37 am

  #283127

  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

• Prassad Dabbada V R

  Ten Centuries

  Points: 1251

  More actions

  February 4, 2014 at 8:49 am

  #1685897

  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

• AndrewSQLDBA

  SSC-Insane

  Points: 21827

  More actions

  February 4, 2014 at 9:09 am

  #1685907

  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

• KS

  Hall of Fame

  Points: 3323

  More actions

  February 4, 2014 at 9:10 am

  #1685908

  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

• KS

  Hall of Fame

  Points: 3323

  More actions

  February 4, 2014 at 9:17 am

  #1685912

  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

• Dwain Camps

  SSC Guru

  Points: 86978

  More actions

  February 4, 2014 at 5:42 pm

  #1686055

  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