Hi Team,
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.
The zip_code_format is in a different table.
I need to validate the zip_code against zip_code_format field and get TRUE/FALSE in a sql or pl/sql.
I would like to pass zip_code and zip_code_format to a pl/sql function to get TRUE/FALSE value in return.
don't know how to start the query. so, I need help with a sql query.
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
Thanks for your help,
I'm assuming that
# => match any numeric digit
A => match any letter
If so, here's one method:
Use regular expressions to convert numbers to # and letters to A.
Then check if the resulting string matches the format you want:
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 zip_code,
zip_code_format,
case
when regexp_replace (
regexp_replace (
zip_code, '[0-9]', '#'
),
'[A-Z]', 'A'
) = zip_code_format then
'TRUE'
else
'FALSE'
end valid
from t;
ZIP_CODE ZIP_CODE_FORMAT VALID
B6789CDF A####AAA TRUE
6789CDF A####AAA FALSE
12345-678 #####-### TRUE
BC345-678 #####-### FALSE
B2C 8A9 A#A #A# TRUE
BbC CA9 A#A #A# FALSE
1234 #### TRUE
123456 #### FALSE
123 45 ### ## TRUE
12345 ### ## FALSE
B3456C A####A TRUE
BBB56C A####A FALSE
12-345 ##-### TRUE
12-34567 ##-### FALSE