Clever Geek Handbook

Oracle, substring on the right and then the left button, odd behavior

Ok, so I have ASN_NO, it could be 30 characters.

I have to take 10 CORRECT most characters. No problems.

SUBSTR(ASN_NO,-10, 10) -- this works fine

But sometimes ASN_NO can be less than 10 characters, in which case I need to superimpose it on 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, if I try to use them in conjunction

LPAD(SUBSTR(ASN_NO,-10, 10),10,'0') -- this gives me a null result when less than 10 but i dont understand why?

So 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 is the last expression using length combined with substring when less than 10 works, but am I overworking this / over that? Does anyone know what's going on in a cleaner way?

+3


source to share


2 answers


You can use a function nvl

, it will be something like:

lpad(nvl(SUBSTR(ASN_NO,-10),asn_no),10,'0')


If the length is less than 10, it SUBSTR(ASN_NO,-10)

returns null, so in this case the nvl function will return the entire string.

As a side note, you don't need to supply the third parameter to get the last 10 characters, substr (ASN_NO, -10) should be sufficient.

+1


source


When you ask for the last 10 characters of a string that is less than 10 characters, you get NULL.

You need LPAD first and then SUBSTR.



Also the second parameter (LENGTH) for SUBSTR is deprecated if you want all characters to end.

+1


source






More articles:


All Articles