'Trimming inconsistent values in a column

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."

sqlssms


Solution 1:[1]

Well you could try something like this:

SELECT t.Chain,
       CASE WHEN t.DC LIKE "DC%" THEN SUBSTRING(t.DC, 3, 2)
            WHEN t.DC LIKE "[0-9][0-9]%" THEN LEFT(t.DC, 2)
       END AS DC,
       t.Item
FROM t

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Pat Jones

Related Questions