Trimming inconsistent values in a column (SQL)

• JeremyU

  SSChasing Mays

  Points: 640

  More actions

  April 7, 2022 at 1:22 am

  #4011923

  I have such an issue.

  On column namely t.DC has sometimes other values after the number for example

  01 ROYAL

  40 ABC

   

  Trying to see if there is anything that can erase those names after digits leaving just the numbers there.

  `

  select t.Chain, t.DC, t.Item#

  FROM t

  `

   

  When I try to write like this " CASE WHEN t.DC IN (01, 02, 08, 21, 22, 30, 32, 40, 55, 62) THEN t.DC ELSE '01' END AS DC," I am getting an error " Conversion failed when converting the nvarchar value '21 CE' to data type int."

  • This topic was modified 2 years, 8 months ago by  JeremyU.
  • This topic was modified 2 years, 8 months ago by  JeremyU.

• Phil Parkin

  SSC Guru

  Points: 246986

  More actions

  April 7, 2022 at 6:44 am

  #4012035

  Try this:

  CASE WHEN LEFT(t.DC,2) IN ('01', '02', '08', '21', '22', '30', '32', '40', '55', '62') 
  THEN LEFT(t.DC,2) 
  ELSE '01' 
  END AS DC

  The absence of evidence is not evidence of absence.
  Martin Rees

  You can lead a horse to water, but a pencil must be lead.
  Stan Laurel

• Madhu g

  Grasshopper

  Points: 14

  More actions

  April 7, 2022 at 10:26 pm

  #4012372

  select t.DC, substring(t.DC,1,charindex(' ',t.DC)) FROM t

  this will remove anything after the space

  t.DC     modifiedt.DC

  

• April 8, 2022 at 5:03 am

  This was removed by the editor as SPAM

• April 25, 2022 at 1:09 pm

  This was removed by the editor as SPAM