I have a column field in the below situation:

I want to normalize this field so that all rows contain 6 digits. The requirements is to go from right to left and add leading zeros if necessary.

The solution I came up with is as follows:

CASE
    WHEN LENGTH(FIELD_NAME) < 6 THEN LPAD(FIELD_NAME, 6, '0')
    WHEN LENGTH(FIELD_NAME) > 6 THEN SUBSTR(FIELD_NAME, -6)
    ELSE FIELD_NAME
END

Is there a more efficient (better) way to achieve the same result?

CodePudding user response：

You can prepend six zeroes and then take the sub-string of the last 6 characters:

SELECT SUBSTR('000000' || value, -6) as short_field_name
FROM   table_name;

Which, for the sample data:

CREATE TABLE table_name (value) AS
SELECT '123' FROM DUAL UNION ALL
SELECT '1234' FROM DUAL UNION ALL
SELECT '12345' FROM DUAL UNION ALL
SELECT '123456' FROM DUAL UNION ALL
SELECT '1234567' FROM DUAL UNION ALL
SELECT '12345678' FROM DUAL UNION ALL
SELECT '123456789' FROM DUAL;

Outputs:

SHORT_FIELD_NAME
000123
001234
012345
123456
234567
345678
456789

db<>fiddle here

CodePudding user response：

Based on the suggest in the comment of @JNevill, this could be decent:

LPAD(SUBSTR(field_name,-LEAST(LENGTH(field_name),6)), 6, '0')