asked on

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???

Aneesh

SELECT RIGHT (urColumn, 3)
FROM urTable

jetli87

ASKER

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

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

Aneesh

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

jetli87

ASKER

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

Open in new window

jetli87

ASKER

***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

Open in new window

Aneesh

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

jetli87

ASKER

hhmmm...no, it gave me:

5101

ASKER CERTIFIED SOLUTION

EugeneZ

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

jetli87

ASKER

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?

EugeneZ

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