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:
,left(Suspect,3) as SuspectOP
,Right(Suspect,2) as SuspectID
5142 = SuspectOP = 514 SuspectID = 42
50528 = SuspectOP = 505 SuspectID = 28
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.