Issues with SUBSTR function Oracle_SQL

Question

I used the SUBSTR function for the similar purposes, but I encountered the following issue:

I am extracting 6 characters from the right, but the data in column is inconsistent and for some rows it has characters less than 6, i.e. 5 or 4. So for such rows, the function returns blanks. How can I fix this?

Example Scenario 1:

SUBSTR('0000123456',-6,6) 

Output: 123456

Scenario 2 (how do I fix this?, I need it to return '23456'):

SUBSTR('23456',-6,6)

Output: ""

Answer 1

You can use a case expression: if the string length is strictly greater than 6 then return just the last 6 characters; otherwise return the string itself. This way you don't need to call substr unless it is really needed.

Alternatively, if speed is not the biggest issue and you are allowed to use regular expressions, you can write this more compactly - select between 0 and 6 characters - as many as possible - at the end of the string.

Finally, if you don't mind using undocumented functions, you can use reverse and standard substr (starting from character 1 and extracting the first 6 characters; that will work as expected even if the string has length less than 6). So: reverse the string, extract first (up to) 6 characters, and then reverse again to restore the order. WARNING: This is shown only for fun; DO NOT USE THIS METHOD!

with
  test_data (str) as (
    select '0123449389' from dual union all
    select '00000000'   from dual union all
    select null         from dual union all
    select 'abcd'       from dual
  )
select str,
       case when length(str) > 6 then substr(str, -6) else str end as case_substr,
       regexp_substr(str, '.{0,6}$')        as regexp_substr,
       reverse(substr(reverse(str), 1, 6))  as rev_substr
from   test_data
;

STR          CASE_SUBSTR    REGEXP_SUBSTR  REV_SUBSTR
----------   -------------  -------------  --------------
0123449389   449389         449389         449389
00000000     000000         000000         000000
                                           
abcd         abcd           abcd           abcd  

Answer 2

One method uses coalesce():

select coalesce(substr('23456', -6, 6), '23456')

Another tweaks the length:

select substr('23456', greatest(- length('23456'), -6), 6)