Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Anuj.

Asked: September 02, 2016 - 5:01 am UTC

Last updated: September 02, 2016 - 3:30 pm UTC

Version: 11.2.0.4

Viewed 1000+ times

You Asked

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?

and Connor said...

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.


Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Small correction

A reader, September 02, 2016 - 7:59 am UTC

Perhaps better
when nvl(replace(translate(str,'0123456789:','0000000000:'),'0'),':') = ':' and length(str) <= 13

because strings without ':' are ok.

Or:
when nvl(translate(str,':0123456789',':'),':') = ':' and length(str) <= 13

Connor McDonald
September 02, 2016 - 3:30 pm UTC

Good point, thanks.

Chris

Thanks

Anuj Parashar, September 05, 2016 - 1:33 pm UTC

Many Thanks Chris and the unknown reviewer from Germany :-)