WHILE / LOOP inside CASE expression SQL – ORACLE

I am testing a select statement containing a CASE expression and I need to add a WHILE / LOOP inside one of the WHEN conditions.

The logic from this statement will eventually be included inside a PL/SQL script but I want to know it´s working 100% before I include it in the procedural.

The logic serves to query a zip/postal code inside an XML package found in a clob column.

INTENDED ACTION:

if ZIP > 5 digits then remove everything after 5th char.

if ZIP < 5 digits then add ‘0’ until the length reaches 5 char.

else do nothing.

WHAT IS NEEDED:

The logic that adds a ‘0’ using contact() function needs to reiterate until the new zip reaches a length of 5 char. As it is, the query only adds up to one ‘0’, but this needs to repeat as many times as required for total char length to = ‘5’. My attempts at adding a LOOP or a WHILE all result in incorrect formating according to Oracle SQL.

QUERY:

SELECT ORDER_NUMBER, 
EXTRACTVALUE(xmltype(PACKAGE), '/Package/fCustomerVO/invoiceAddress/zip') as "ZIPS_INV",
LENGTH(EXTRACTVALUE(xmltype(PACKAGE), '/Package/fCustomerVO/invoiceAddress/zip')) as "ZIPS_LEN",
CASE
/* add digits to zip WHILE/LOOP NEEDED HERE */
  WHEN LENGTH(EXTRACTVALUE(xmltype(PACKAGE), '/Package/fCustomerVO/invoiceAddress/zip')) < 5 
    THEN concat(EXTRACTVALUE(xmltype(PACKAGE), '/Package/fCustomerVO/invoiceAddress/zip'), '0')
/* subtract digits to zip */
  WHEN LENGTH(EXTRACTVALUE(xmltype(PACKAGE), '/Package/fCustomerVO/invoiceAddress/zip')) > 5 
    THEN substr(EXTRACTVALUE(xmltype(PACKAGE), '/Package/fCustomerVO/invoiceAddress/zip'), 1, 5)    
  ELSE 
    'correct format'
END as "FIXED_ZIP"
FROM quarantine;

Answer

You can do both operations at once with RPAD(): this functions either pads the string, truncates it or leaves it untouched as per the target length.

So:

RPAD(
    EXTRACTVALUE(xmltype(PACKAGE), '/Package/fCustomerVO/invoiceAddress/zip'),
    5,
    '0'
) 

In your query:

SELECT
    t.*,
    LENGTH(zips_inv) zip_len,
    RPAD(zip_inv, 5, '0') fixed_zip
    CASE WHEN LENGTH(zips_inv) = 5
        THEN 'correct format'
        ELSE 'wrong format' 
    END zip_status
FROM (
    SELECT 
        order_number, 
        EXTRACTVALUE(xmltype(PACKAGE), '/Package/fCustomerVO/invoiceAddress/zip') zips_inv
    FROM quarantine
) t

I find that a subquery is useful here to avoid repeating the same lengthy expression multiple times. Also, separating the zip value from its status (correct or not) makes more sense to me.