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 3 years, 5 months ago by JeremyU.
  • This topic was modified 3 years, 5 months ago by JeremyU.

• Phil Parkin

  SSC Guru

  Points: 247180

  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

  ---

• 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