boster281 boster281 - 1 month ago 19
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

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.

Comments