Hi,
I am trying to define a regular expression pattern which satisfies the following criteria for string match:
String Length: Maximum 13 characters
Allowed Characters: Any digit (0-9) and ":"
Condition: ":" can occur only once, digits can repeat.
I came up with crude solution:
with
tbl as (select '1234567891234' str from dual union
select '123:123456789' from dual union
select '12345:::91234' from dual union
select '1234567891234' from dual union
select '123456789123a' from dual union
select '145:::91234' from dual union
select ':834:' from dual union
select ':123459123' from dual union
select '1:3433434591234' from dual union
select ':834:' from dual union
select '5834' from dual union
select '1234:59:13' from dual union
select ':578834' from dual union
select '12345913:' from dual union
select ':::45678:1234' from dual)
select str,
length(str),
regexp_count(str, '(^[0-9]{1,13}$|^:[0-9]{1,12}$|^[0-9]{1,1}:[0-9]{1,11}$|^[0-9]{1,2}:[0-9]{1,10}$|^[0-9]{1,3}:[0-9]{1,9}$|^[0-9]{1,4}:[0-9]{1,8}$|^[0-9]{1,5}:[0-9]{1,7}$|^[0-9]{1,6}:[0-9]{1,6}$|^[0-9]{1,7}:[0-9]{1,5}$|^[0-9]{1,8}:[0-9]{1,4}$|^[0-9]{1,9}:[0-9]{1,3}$|^[0-9]{1,10}:[0-9]{1,2}$|^[0-9]{1,11}:[0-9]{1,1}$|^[0-9]{1,12}:$)') str_match
from tbl;
I want to use this regex pattern as configuration data (stored in a configuration table). Is there any better way of writing this pattern match expression?
Are you forced to use regex ? Maybe something like this
SQL> with
2 tbl as (select '1234567891234' str from dual union
3 select '123:123456789' from dual union
4 select '12345:::91234' from dual union
5 select '1234567891234' from dual union
6 select '123456789123a' from dual union
7 select '145:::91234' from dual union
8 select ':834:' from dual union
9 select ':123459123' from dual union
10 select '1:3433434591234' from dual union
11 select ':834:' from dual union
12 select '5834' from dual union
13 select '1234:59:13' from dual union
14 select ':578834' from dual union
15 select '12345913:' from dual union
16 select ':::45678:1234' from dual)
17 select str,
18 length(str),
19 case
20 when replace(translate(str,'0123456789:','0000000000:'),'0') = ':' and length(str) <= 13
21 then 'Y' else 'N'
22 end valid
23 from tbl;
STR LENGTH(STR) V
--------------- ----------- -
1234567891234 13 N
123456789123a 13 N
12345913: 9 Y
12345:::91234 13 N
1234:59:13 10 N
123:123456789 13 Y
145:::91234 11 N
1:3433434591234 15 N
5834 4 N
:123459123 10 Y
:578834 7 Y
:834: 5 N
:::45678:1234 13 N
13 rows selected.