How can I use LEFT & RIGHT Functions in SQL to get

问题:

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

回答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.

回答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)

回答3:

Here an alternative using SUBSTRING

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

with fiddle

回答4:

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.