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?