Skip to Main Content

Regexp_substr unexpected behaviour

NicoletteNov 2 2007 — edited Nov 5 2007

While trying to come up with a constraint to check for correct RGB color codes
(RvalueGvalueBvalue) where the value is between 0 and 255
I saw the following:

database used: Oracle Database 10g Express Edition Release 10.2.0.1.0

CREATE TABLE test
(color varchar2(12)
,status varchar2(1)
)

insert into test 
values ('R2G2B2','G')

insert into test
values ('R22G22B22','G')

insert into test
values ('R222G222B222','G')

insert into test
values ('R300G2B2','F')

insert into test
values ('R300G256B2','F')

insert into test
values ('R300G256B256','F')

select t.status
     , t.color
     , REGEXP_SUBSTR(t.color,'^R(\d{1,2}|1\d{2}|2[0-4]\d{1}|25[0-5])') red
     , REGEXP_SUBSTR(t.color,'G(\d{1,2}|1\d{2}|2[0-4]\d{1}|25[0-5])') green
     , REGEXP_SUBSTR(t.color,'B(\d{1,2}|1\d{2}|2[0-4]\d{1}|25[0-5])$') blue
     , REGEXP_SUBSTR(t.color,'^R(\d{1,2}|1\d{2}|2[0-4]\d{1}|25[0-5])G(\d{1,2}|1\d{2}|2[0-4]\d{1}|25[0-5])B(\d{1,2}|1\d{2}|2[0-4]\d{1}|25[0-5])$','1','1','i') total 
from test t
order by red

output

status   color        red    green    blue  total
  G      R2G2B2       R2      G2       B2    R2G2B2
  G      R22G22B22    R22     G22      B22   R22G22B22
  G      R222G222B222 R22     G22      B222  R222G222B222
  F      R300G2B2     R30     G2       B2    
  F      R300G300B2   R30     G30      B2
  F      R300G300B300 R30     G30

Expected output

status   color        red    green    blue  total
  G      R2G2B2       R2      G2       B2    R2G2B2
  G      R22G22B22    R22     G22      B22   R22G22B22
  G      R222G222B222 R222    G222     B222  R222G222B222
  F      R300G2B2             G2       B2    
  F      R300G300B2                    B2
  F      R300G300B300 

Both the total and blue colums have the output as expected
but the red and the green give a output with a maximum of 2 digits instead of 3
even when the string should not match (300 > 255).

I have read the information about regular expressions in the Application Developer's Guide - Fundamentals and SQL Reference but neither explain above behaviour

Can someone explain why this is happening?
Or give a hint of how to correct the select statement in such a way that results are like the expected output?

Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked due to inactivity on Dec 3 2007
Added on Nov 2 2007
6 comments
1,909 views