Separate two Values

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

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

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

  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