Solved

SQL Query : extracting last 3 digits

Posted on 2009-05-12
10
1,008 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

 
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 43

Accepted Solution

by:
Eugene Z 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 43

Expert Comment

by:Eugene Z
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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Azure Functions is a solution for easily running small pieces of code, or "functions," in the cloud. This article shows how to create one of these functions to write directly to Azure Table Storage.
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

707 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