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

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
Asked:
jetli87
  • 5
  • 3
  • 2
1 Solution
 
Aneesh RetnakaranDatabase AdministratorCommented:
SELECT RIGHT (urColumn, 3)
FROM urTable
0
 
jetli87Author 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
 
Aneesh RetnakaranDatabase AdministratorCommented:
>I've tried that, but it doesn't display consistently.
Can u post some sample
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
jetli87Author 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 

Open in new window

0
 
jetli87Author 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   

Open in new window

0
 
Aneesh RetnakaranDatabase AdministratorCommented:
SELECT RIGHT( 65101, 4 )  --- is that giving you 1 ??
0
 
jetli87Author Commented:
hhmmm...no, it gave me:

5101
0
 
Eugene ZCommented:
is sunitcode not numeric?

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

0
 
jetli87Author 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
 
Eugene ZCommented:
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.

Join & Write a Comment

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 5
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now