• Status: Solved
• Priority: Medium
• Security: Public
• Views: 1034

SQL Query : extracting last 3 digits

Hi SQL experts,

here's a simple one.  I need to extract the last 3 digits of a column, but the numbers of digits in each column varies.

Column:
111101
2202
333333303

Results to equal:
101
202
303

How???
0
jetli87
• 5
• 3
• 2
1 Solution

SELECT RIGHT (urColumn, 3)
FROM urTable
0

Author Commented:
I've tried that, but it doesn't display consistently.

I tried left() and right() and it doesn't get the ideal output.
0

>I've tried that, but it doesn't display consistently.
Can u post some sample
0

Author Commented:
Here's the first query with a filter for a specific set of records
``````***Normal Return***
select sunitcode from tenant where hproperty = 170

sunitcode
6401101
6401102
6402211
6402213
6402214
6402215
6402216

***With Right()***
select sunitcode=right(sunitcode,4) from tenant where hproperty = 170

101
102
211
213
214
215
216
``````
0

Author Commented:
***Here's the same query but with a different set of records***

``````***Normal Return***
select sunitcode from tenant where hproperty = 172

65101
65102
65127
65128
65129
65130

***With Right()***
select sunitcode=right(sunitcode,4) from tenant where hproperty = 172

1
2
7
8
9
0
``````
0

SELECT RIGHT( 65101, 4 )  --- is that giving you 1 ??
0

Author Commented:
hhmmm...no, it gave me:

5101
0

Commented:
is sunitcode not numeric?

---try
select sunitcode=right(rtrim(sunitcode),3) from tenant where hproperty = 172

0

Author Commented:
worked like a charm.

I'm still learning Sql so i'm considerly novice, so can you explain briefly the conext of the statement you supplied and how it resolved the issue?
0

Commented:
so it was numeric..

can be :
1. it is char datatype ->
2. it is char(varchar,etc) datatype and data was pumped with  trailing blanks.
3. etc

more:

RTRIM
http://msdn.microsoft.com/en-us/library/aa238471(SQL.80).aspx

datatypes
http://msdn.microsoft.com/en-us/library/aa258271(SQL.80).aspx
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.