How can I use LEFT & RIGHT Functions in SQL to get last 3 characters?

Question

I have a Char(15) field, in this field I have the data below:

94342KMR
947JCP
7048MYC

I need to break down this, I need to get the last RIGHT 3 characters and I need to get whatever is to the LEFT. My issue is that the code on the LEFT is not always the same length as you can see.

How can I accomplish this in SQL?

Thank you

Answer 1

SELECT  RIGHT(RTRIM(column), 3),
        LEFT(column, LEN(column) - 3)
FROM    table

Use RIGHT w/ RTRIM (to avoid complications with a fixed-length column), and LEFT coupled with LEN (to only grab what you need, exempt of the last 3 characters).

if there's ever a situation where the length is <= 3, then you're probably going to have to use a CASE statement so the LEFT call doesn't get greedy.

Answer 2

You can use RTRIM or cast your value to VARCHAR:

SELECT RIGHT(RTRIM(Field),3), LEFT(Field,LEN(Field)-3)

Or

SELECT RIGHT(CAST(Field AS VARCHAR(15)),3), LEFT(Field,LEN(Field)-3)