sqlsql-servert-sql

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

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

Solution 
  • 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.