CODEWITHSUNDEEP
sqlteradata
user3901666
user3901666

Reputation: 409

Substr in reverse direction

I am trying to get the numbers from a numerics field (which is mostly 8 characters long) from the last digit back to 6 digits in reverse direction. E.g

2345678-- i want 345678
56789356--789356

I am using the below code:

sel SUBSTRING( LEVN010  from character_length(LEVN010)-5 for   6 )  FROM X.TABLE

But, this is not working. Any solution for this is much appreciated.

Thanks, Amit

Upvotes: 0

Views: 9975

Answers (2)

dnoeth
dnoeth

Reputation: 60472

Your code is correct for a VarChar column, but you don't need to specifiy FOR, it's optional and defaults to until the end:

SUBSTRING( LEVN010  from character_length(LEVN010)-5)

In TD15.10 the function RIGHT is supported:

RIGHT(LEVN010, 6)

If LEVN010 is a fixed length CHAR there are probably trailing spaces and you need to TRIM them (I added the FOR back to get rid of the trailing spaces):

SUBSTRING( LEVN010  from character_length(TRIM(LEVN010))-5 FOR 6)

If LEVN010 is an INTEGER you better use MOD:

LEVN010 mod 1000000

Upvotes: 1

fonfonx
fonfonx

Reputation: 1465

Use the RIGHT function:

SELECT RIGHT(LEVN010,6) FROM X.table

or use

SELECT SUBSTRING(LEVN010,-6) FROM X.table

Upvotes: 2

Related Questions