15

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

gofr1
  • 15,741
  • 11
  • 42
  • 52
jorame
  • 2,147
  • 12
  • 41
  • 58
  • 1
    So, since this is a `char(15)` what are you expected results? All blanks or the last 3 non blank chars? – Jodrell Jul 01 '13 at 16:48
  • @KenWhite: OP uses [`sql-server`](http://stackoverflow.com/questions/tagged/sql-server) not [`mysql`](http://stackoverflow.com/questions/tagged/mysql). – Brad Christie Jul 01 '13 at 16:49
  • You're right. I misread the tag. My fault. :-( Must be Monday here. – Ken White Jul 01 '13 at 16:53
  • 1
    What you really want here is two columns, at least one of them should be varchar or nvarchar. – Jodrell Jul 01 '13 at 16:58

4 Answers4

14
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.

Brad Christie
  • 100,477
  • 16
  • 156
  • 200
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)
Lamak
  • 69,480
  • 12
  • 108
  • 116
1

Here an alternative using SUBSTRING

SELECT
            SUBSTRING([Field], LEN([Field]) - 2, 3) [Right3],
            SUBSTRING([Field], 0, LEN([Field]) - 2) [TheRest]
    FROM 
            [Fields]

with fiddle

Jodrell
  • 34,946
  • 5
  • 87
  • 124
1
select right(rtrim('94342KMR'),3)

This will fetch the last 3 right string.

select substring(rtrim('94342KMR'),1,len('94342KMR')-3)

This will fetch the remaining Characters.

Arun A S
  • 6,421
  • 4
  • 29
  • 43
user2127106
  • 11
  • 1