Trimming inconsistent values in a column (SQL)

Question

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

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

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

Phil Parkin SSC Guru Points: 246960 More actions · Answer 1

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
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Madhu g Grasshopper Points: 14 More actions · Answer 2

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

this will remove anything after the space

t.DC modifiedt.DC