Ok, so i have an ASN_NO, it can be 30 chars.
I'm required to take the 10 RIGHT most characters of it. No problem.
SUBSTR(ASN_NO,-10, 10) -- this works fine
But sometimes the ASN_NO can be less than 10 characters, and in that case i need to pad it with left zeros.
LPAD(ASN_NO,10,'0') -- this works when less than 10 characters, except when having an ASN_NO greater it substrings from the left
So then if i try to use them in conjuction
LPAD(SUBSTR(ASN_NO,-10, 10),10,'0') -- this gives me a null result when less than 10 but i dont understand why?
So then i came up with this:
LPAD(SUBSTR(ASN_NO, CASE WHEN LENGTH(SI.ASN_NO) >= 10 THEN -10 ELSE -LENGTH(ASN_NO) END, 10),10,'0')
This last statement using length in conjunction with substring when less that 10 works...but am i overworking this/over thinking this? Anyone know whats going on an a cleaner way?
You can use nvl function, will be something like:
lpad(nvl(SUBSTR(ASN_NO,-10),asn_no),10,'0')
In case that length is lower than 10 then SUBSTR(ASN_NO,-10) return null, so in this case nvl function will return the entire string.
As a note, you don't need to specify third parameter to get last 10 chars, substr(ASN_NO,-10) should be enough.