Substring from right? 1

[DugsDMan]

Is there a keyword I can use to grab the last n characters in a field? I can do it using substring and octet length and such, but figured there has to be a better way.

Anyone know?

Thanks!

 

[jgerstb]

How about this to get the last 3 characters from a column

SELECT SUBSTRING(TRIM(column) FROM CHARACTER_LENGTH(TRIM(column)) -2 FOR 3) from Table

If column is CHAR(10) and contains "WEST "
TRIM removes trailing spaces
CHARACTER_LENGTH returns 4
subtract 2 from CHARACTER_LENGTH = 2
SUBSTRING FROM 2 FOR 3 gives "EST"

 

[DugsDMan]

Thanks jgerstb. I knew it could be done with lengths and such, just figured there'd be some keyword. In programming, all language I've used have some kind of left, right, mid functionality. But, I've never had to try it in SQL. Just curious if one existed.

Thanks!

 

[liangxfg]

the right way is :
substr ( string,char(string)-n,n+1)

 

[DugsDMan]

I tried that and it just gives me the whole field.

Here's what I'm trying to do. I have a field that I need to get codes from. The source data comes from multiple sources. The field is defined as char(5), but can be 3-5 digits. All I need are the last 4 digits (if there are 4 there). If it's 3, I want all 3. If it's 4, I want all 4. If it's 5, then I only want the 4 on the right.

Ex:
Column
123
4567
12345

I would want to get this back:
123
4567
2345

Do you know how I can do this?

Thanks!

 

[dnoeth]

select cast(col as integer) mod 10000

Dieter

 

[DugsDMan]

Go idea Dieter! I'll try that when I can log on again. Their doing maintenance now, so I can't check it. Seems like it should work just fine though.

Thanks!

 

[DugsDMan]

Guess that should have been "Good idea"

I tried it on our dev system and it worked great.

Thanks Dieter!!