boster281 boster281 - 1 year ago 85
SQL Question

Last 1 to 2 digits of a int with a varible length int

I have an int that is 4 to 5 characters long.
I have a report that cast the first 3 digits as the location and last 1 to 2 digits as a cause.
So this is how they look

5142 = 514 = paint line 2 = paint to thin:
50528 = 505 = machining 28 = oblong hole:

SELECT [Suspect]
,left(Suspect,3) as SuspectOP
,Right(Suspect,2) as SuspectID


This query will return

5142 = SuspectOP = 514 SuspectID = 42
50528 = SuspectOP = 505 SuspectID = 28


So what i want is to read everything after the first 3 digits of the int.

Answer Source

You could use a conditional operators based on the length like this:

SELECT 
    [Suspect]
    , SuspectOP = LEFT(Suspect,3)
    , SuspectID = CASE 
                     WHEN LEN(Suspect) = 5 THEN RIGHT(Suspect,2) 
                     ELSE RIGHT(Suspect, 1) 
                  END

Mind you, it's not ideal, you should really keep the values separate if your use case is like the one mentioned.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download