CODEWITHSUNDEEP
oracle-databasesubstr
SomeRandomDeveloper
SomeRandomDeveloper

Reputation: 489

Oracle, substring from right then pad left, odd behavior

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?

Upvotes: 3

Views: 6247

Answers (2)

Aramillo
Aramillo

Reputation: 3216

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.

Upvotes: 1

Martin Drautzburg
Martin Drautzburg

Reputation: 5243

When you ask for the last 10 characters of a string which has less than 10 characters you get NULL.

You need to LPAD first and then SUBSTR.

Also the second parameter (LENGTH) to SUBSTR is obsolete when you want to get all characters up to the end.

Upvotes: 2

Related Questions