Need String Format Validation in Oracle SQL

Question

I need to validate the given string for a given format. I have a table as below.

with t as (
select 'AR' country,8 zip_code_length,'A####AAA' zip_code_format,'B6789CDF' zip_code from dual union all
select 'AR' country,8 zip_code_length,'A####AAA' zip_code_format,'6789CDF' zip_code from dual union all
select 'BR' country,9 zip_code_length,'#####-###' zip_code_format,'12345-678' zip_code from dual union all
select 'BR' country,9 zip_code_length,'#####-###' zip_code_format,'BC345-678' zip_code from dual union all
select 'CA' country,7 zip_code_length,'A#A #A#' zip_code_format,'B2C 8A9' zip_code  from dual union all
select 'CA' country,7 zip_code_length,'A#A #A#' zip_code_format,'BbC CA9' zip_code  from dual union all
select 'CH' country,4 zip_code_length,'####' zip_code_format,'1234' zip_code from dual union all
select 'CH' country,4 zip_code_length,'####' zip_code_format,'123456' zip_code from dual union all
select 'CZ' country,6 zip_code_length,'### ##' zip_code_format,'123 45' zip_code from dual union all
select 'CZ' country,6 zip_code_length,'### ##' zip_code_format,'12345' zip_code from dual union all
select 'EC' country,6 zip_code_length,'A####A' zip_code_format,'B3456C' zip_code from dual union all
select 'EC' country,6 zip_code_length,'A####A' zip_code_format,'BBB56C' zip_code from dual union all
select 'PL' country,6 zip_code_length,'##-###' zip_code_format,'12-345' zip_code from dual union all
select 'PL' country,6 zip_code_length,'##-###' zip_code_format,'12-34567' zip_code from dual
)
select * from t;

I have zip_code_format and zip_code fields in the above query.

I need to validate the zip_code against zip_code_format field and get TRUE/FALSE in SQL or PL/SQL.

I would like to have a PL/SQL function where zip_code and zip_code_format will be the parameters and result would be TRUE/FALSE in return.

Expected output:

country zip_code_length zip_code_format zip_code    validation
AR      8               A####AAA        B6789CDF    TRUE
AR      8               A####AAA        6789CDF     FALSE
BR      9               #####-###       12345-678   TRUE
BR      9               #####-###       BC345-678   FALSE
CA      7               A#A #A#         B2C 8A9     TRUE
CA      7               A#A #A#         BbC CA9     FALSE
CH      4               ####            1234        TRUE
CH      4               ####            123456      FALSE
CZ      6               ### ##          123 45      TRUE
CZ      6               ### ##          12345       FALSE
EC      6               A####A          B3456C      TRUE
EC      6               A####A          BBB56C      FALSE
PL      6               ##-###          12-345      TRUE
PL      6               ##-###          12-34567    FALSE

You need to go read up on regular expressions

OldProgrammer
– OldProgrammer

2021-03-17 18:22:08 +00:00
Commented Mar 17, 2021 at 18:22 — OldProgrammer
– OldProgrammer, Commented Mar 17, 2021 at 18:22

MT0 · Accepted Answer · 2021-03-17 18:54:21Z

You can replace the # in the zip_code_format with \d and A with [A-Z] and wrap it in ^ and $ to match the start and end of the strings and then just compare it to the zip_code using REGEXP_LIKE:

with t as (
select 'AR' country,8 zip_code_length,'A####AAA' zip_code_format,'B6789CDF' zip_code from dual union all
select 'AR' country,8 zip_code_length,'A####AAA' zip_code_format,'6789CDF' zip_code from dual union all
select 'BR' country,9 zip_code_length,'#####-###' zip_code_format,'12345-678' zip_code from dual union all
select 'BR' country,9 zip_code_length,'#####-###' zip_code_format,'BC345-678' zip_code from dual union all
select 'CA' country,7 zip_code_length,'A#A #A#' zip_code_format,'B2C 8A9' zip_code  from dual union all
select 'CA' country,7 zip_code_length,'A#A #A#' zip_code_format,'BbC CA9' zip_code  from dual union all
select 'CH' country,4 zip_code_length,'####' zip_code_format,'1234' zip_code from dual union all
select 'CH' country,4 zip_code_length,'####' zip_code_format,'123456' zip_code from dual union all
select 'CZ' country,6 zip_code_length,'### ##' zip_code_format,'123 45' zip_code from dual union all
select 'CZ' country,6 zip_code_length,'### ##' zip_code_format,'12345' zip_code from dual union all
select 'EC' country,6 zip_code_length,'A####A' zip_code_format,'B3456C' zip_code from dual union all
select 'EC' country,6 zip_code_length,'A####A' zip_code_format,'BBB56C' zip_code from dual union all
select 'PL' country,6 zip_code_length,'##-###' zip_code_format,'12-345' zip_code from dual union all
select 'PL' country,6 zip_code_length,'##-###' zip_code_format,'12-34567' zip_code from dual
)
SELECT COUNTRY,
       LENGTH( zip_code_format ) AS zip_code_length,
       zip_code_format,
       zip_code,
       CASE
       WHEN REGEXP_LIKE(
              zip_code,
              '^'
              || REPLACE(
                   REPLACE(
                     zip_code_format,
                     '#',
                     '\d'
                   ),
                   'A',
                   '[A-Z]'
                 )
              || '$'
            )
       THEN 'TRUE'
       ELSE 'FALSE'
       END AS validation
FROM   t;

Which outputs:

COUNTRY | ZIP_CODE_LENGTH | ZIP_CODE_FORMAT | ZIP_CODE  | VALIDATION
:------ | --------------: | :-------------- | :-------- | :---------
AR      |               8 | A####AAA        | B6789CDF  | TRUE      
AR      |               8 | A####AAA        | 6789CDF   | FALSE     
BR      |               9 | #####-###       | 12345-678 | TRUE      
BR      |               9 | #####-###       | BC345-678 | FALSE     
CA      |               7 | A#A #A#         | B2C 8A9   | TRUE      
CA      |               7 | A#A #A#         | BbC CA9   | FALSE     
CH      |               4 | ####            | 1234      | TRUE      
CH      |               4 | ####            | 123456    | FALSE     
CZ      |               6 | ### ##          | 123 45    | TRUE      
CZ      |               6 | ### ##          | 12345     | FALSE     
EC      |               6 | A####A          | B3456C    | TRUE      
EC      |               6 | A####A          | BBB56C    | FALSE     
PL      |               6 | ##-###          | 12-345    | TRUE      
PL      |               6 | ##-###          | 12-34567  | FALSE

If you want it in PL/SQL then just do exactly the same inside a function:

CREATE FUNCTION validate_zip_code(
  in_zip_code        IN VARCHAR2,
  in_zip_code_format IN VARCHAR2
) RETURN VARCHAR2 DETERMINISTIC
IS
BEGIN
  IF REGEXP_LIKE(
       in_zip_code,
       '^'
       || REPLACE(
         REPLACE(
           in_zip_code_format,
           '#',
           '\d'
         ),
         'A',
         '[A-Z]'
       )
       || '$'
     )
  THEN RETURN 'TRUE';
  ELSE RETURN 'FALSE';
  END IF;
END;
/

Then, for the same data:

SELECT COUNTRY,
       LENGTH( zip_code_format ) AS zip_code_length,
       zip_code_format,
       zip_code,
       validate_zip_code( zip_code, zip_code_format ) AS validation
FROM   t;

Outputs:

COUNTRY | ZIP_CODE_LENGTH | ZIP_CODE_FORMAT | ZIP_CODE  | VALIDATION
:------ | --------------: | :-------------- | :-------- | :---------
AR      |               8 | A####AAA        | B6789CDF  | TRUE      
AR      |               8 | A####AAA        | 6789CDF   | FALSE     
BR      |               9 | #####-###       | 12345-678 | TRUE      
BR      |               9 | #####-###       | BC345-678 | FALSE     
CA      |               7 | A#A #A#         | B2C 8A9   | TRUE      
CA      |               7 | A#A #A#         | BbC CA9   | FALSE     
CH      |               4 | ####            | 1234      | TRUE      
CH      |               4 | ####            | 123456    | FALSE     
CZ      |               6 | ### ##          | 123 45    | TRUE      
CZ      |               6 | ### ##          | 12345     | FALSE     
EC      |               6 | A####A          | B3456C    | TRUE      
EC      |               6 | A####A          | BBB56C    | FALSE     
PL      |               6 | ##-###          | 12-345    | TRUE      
PL      |               6 | ##-###          | 12-34567  | FALSE

db<>fiddle here

@MTO, looks like a good suggestion. I will test and get back to you. Thanks

Gary_W · Accepted Answer · 2021-03-17 22:03:25Z

Comment to MT0's answer but formatting in a comment just does not work so putting this here. Since the length of the zip_code is supplied, I'd like to suggest doing the more expensive string operations only if the length of the zip_code = the length supplied, otherwise it's automatically false. Note this would allow you better error reporting too, as now you can differentiate between just FALSE in general with FALSE for length didn't match vs FALSE for format didn't match. Your support folks will thank you.

...
CASE 
  WHEN LENGTH(ZIP_CODE) = zip_code_length THEN
    CASE
      WHEN  REGEXP_LIKE(ZIP_CODE,'^'||
            REPLACE(REPLACE(zip_code_format,'#','\d'),'A','[A-Z]')||'$')
        THEN 'TRUE'
        ELSE 'FALSE'  -- Format didn't match
    END 
  ELSE 'FALSE'  -- Length of zip not correct
END AS VALIDATION

Collectives™ on Stack Overflow

Need String Format Validation in Oracle SQL

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related