help on substring (getting last 4 digits)

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

help on substring (getting last 4 digits)

Author

Topic

nasman
Starting Member

15 Posts

Posted - 2012-02-04 : 04:12:07

i need syntax for extracting only last 4 digit, the problem is the string im working on are not uniformed in length here is the sample data below

emp_ID
---------
12345678
1234567
123456
12345

needed result must be

emp_ID
----------
5678
4567
3456
2345

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-02-04 : 04:23:05

[code]SELECT emp_ID%10000[/code]

Kristen
Test

22859 Posts

Posted - 2012-02-04 : 05:16:04

"the problem is the string im working on "

If [emp_ID] is a string, rather than an integer, then

SELECT RIGHT(emp_ID, 4)

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-02-04 : 08:50:03

To be fair Kristen, it was 4:12 AM in the morning my time, and I had been up for 24 hours straight, so my ability to read properly was a little impaired .)

Kristen
Test

22859 Posts

Posted - 2012-02-04 : 13:23:54

To be fair ... I sure as eggs-are-eggs hope that this is NOT being stored as a String ...

nasman
Starting Member

15 Posts

Posted - 2012-02-05 : 20:26:31

the actual data looks like this, its in character format

column_1
------------------------------------
MIGS txn%23 5%2c auth id%3a T22593
MIGS txn# 2000000019, auth id: 174898
MIGS txn# 2000000017, auth id: T72780
MIGS txn# 10, auth id: 003095
MIGS txn# 11, auth id: 006129

i only need to extract the last 6 characters.

cheers

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2012-02-05 : 20:31:07

4 or 6? You said 4 in your first post, 6 in a latter one, column names also changed.

As Kristen said...

SELECT RIGHT(emp_id, 4) FROM <table name>
or
SELECT RIGHT(column_1, 6) FROM <table name>

Depending whether it's 4 or 6 and depending what the column names really are.

--
Gail Shaw
SQL Server MVP

nasman
Starting Member

15 Posts

Posted - 2012-02-05 : 20:45:56

sorry for the inconsistencies, i just made up a simple example on my 1st post.

this RIGHT(COLUMN NAME, length) syntax is not working on my part, it just changed the alignment of the column, am i doing something wrong here?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-05 : 20:50:55

quote:
Originally posted by nasman

sorry for the inconsistencies, i just made up a simple example on my 1st post.

this RIGHT(COLUMN NAME, length) syntax is not working on my part, it just changed the alignment of the column, am i doing something wrong here?

would you mind showing your original query as it seems like there's something else in it which you've not explained so far

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

nasman
Starting Member

15 Posts

Posted - 2012-02-05 : 20:57:00

here is the actual code where PAYTYPE refers to column_1

SELECT t1.ProductDesc,
t2.DealCode,
t2.Updated,
(RIGHT(t2.PayType,6)) AS REF_NO,
t2.Amount,
(CASE
WHEN SUBSTR(t2.PayType,1,4) = 'MIGS' THEN "Credit Card"
ELSE "OTC"
END) AS TENDER_TYPE,
(CASE
WHEN SUBSTR(t2.PayType,1,4) = 'MIGS' THEN "BDO"
ELSE "Dragon Pay"
END
) AS GATEWAY_NAME,
(DATE(t2.Updated)) FORMAT=MMDDYY8. AS APPROVAL_DATE,
t2.SMACNumber,
(" ") AS STATUS,
(" ") AS PAYMENT_FILENAME
FROM WORK.DEALS AS t1 INNER JOIN WORK.TRANSACTIONS AS t2 ON (t1.PAFCode = t2.PAFCode)
ORDER BY TENDER_TYPE;

Kristen
Test

22859 Posts

Posted - 2012-02-06 : 03:05:33

Maybe there are trailing spaces on the end of the [PAYTYPE] column? Is:


... (RIGHT(RTrim(t2.PayType),6)) AS REF_NO,

any better? (that won't work if there are trailing TABs or other control characters)

If not please give an example of


SELECT ']' + t2.PayType + '[', ']' + RIGHT(t2.PayType, 6) + '['

nasman
Starting Member

15 Posts

Posted - 2012-02-06 : 04:59:02

im working with SAS and SAS doesnt support rtrim and ltrim function

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2012-02-06 : 05:21:56

SAS? What's that?

--
Gail Shaw
SQL Server MVP

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-06 : 09:25:23

quote:
Originally posted by nasman

im working with SAS and SAS doesnt support rtrim and ltrim function

so what ever you explained was sql query written inside SAS? then i think you've check its documentation to see whats the corresponding function available in it

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

nasman
Starting Member

15 Posts

Posted - 2012-02-06 : 21:03:02

thanks for the effort. kudos to you guys and sql team, tried a work around, but its in case syntax and its dumb long code. but i will use it in teradata sql assistant and extract it there and import it on SAS. again... thanks

nasman
Starting Member

15 Posts

Posted - 2012-02-06 : 22:04:45

ok i found another solution, someone might see this useful

this is what i did

1. LEFT(REVERSE(column_name)) as column_name2
2. SUBSTR(column_name2,1,6) as column_name3
3. LEFT(REVERSE(column_name3))

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources