Solved

SQL Query : extracting last 3 digits

Posted on 2009-05-12
10
1,003 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Stored Proc - Rewrite 42 59
TSQL XML Namespaces 7 23
First Max value 3 29
Comparison query - 4 columns 9 25
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
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…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

860 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