getting rightmost characters in a field in SQL without RIGHT()

Question

i'm working on an old AS400 that does not provide support for the SQL RIGHT() function, and i need to SELECT rows based on the X rightmost characters of a number that might be either 7 or 8 characters long using SQL.

How can i get what i want if i cant use RIGHT and i don't know the exact length of the number?.

here is some context in the off chance it's usefull:

The number is a date but was stored as a number, so the first zero is stripped away by the database, causing dates starting with zero like 01032016 [read as 01-03-2016] to be trimmed to 1032016).

the database is quite big so requesting all the rows without filtering this field consumes quite a bit of resources and time.

Changing the format of the dates in the database to something more reasonable would cause breaking changes on software I dont maintain and that is mission critial.

Answer 1

If the data was stored as a number, then the DB doesn't strip away the lead digits. Whatever tool you're using to query the data is doing that.

Assuming the data was stored as a packed or zoned decimal and not integer (very likely), then use DIGITS() to convert the number to fixed character including the lead zeros and then you can use SUBSTR()

Answer 2

SUBSTRING(mystr,character_length(mystr)-x+1,x)

or

SUBSTRING(mystr,character_length(mystr)-x+1)