Solved

SQL Query : extracting last 3 digits

Posted on 2009-05-12
10
1,002 Views
Last Modified: 2012-05-06
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
Comment
Question by:jetli87
  • 5
  • 3
  • 2
10 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 24369418
SELECT RIGHT (urColumn, 3)
FROM urTable
0
 
LVL 1

Author Comment

by:jetli87
ID: 24369435
I've tried that, but it doesn't display consistently.

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

Expert Comment

by:Aneesh Retnakaran
ID: 24369441
>I've tried that, but it doesn't display consistently.
Can u post some sample
0
Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

 
LVL 1

Author Comment

by:jetli87
ID: 24369471
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
 
LVL 1

Author Comment

by:jetli87
ID: 24369497
***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
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 24369571
SELECT RIGHT( 65101, 4 )  --- is that giving you 1 ??
0
 
LVL 1

Author Comment

by:jetli87
ID: 24369580
hhmmm...no, it gave me:

5101
0
 
LVL 42

Accepted Solution

by:
EugeneZ earned 500 total points
ID: 24369585
is sunitcode not numeric?

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

0
 
LVL 1

Author Comment

by:jetli87
ID: 24369600
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
 
LVL 42

Expert Comment

by:EugeneZ
ID: 24369696
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

Featured Post

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

776 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question