The record has nothing in the errors field. The commented out code doesn't return anything but when the variable is used it returns a record. What am I doing wrong?
test varchar(5);
test := '1';
select * from timedetail
where empnum = '013061361' and tsdate = '1-nov-13'
--and regexp_like(errors, '[1,0]')
and regexp_like(errors, '[ || test || ]')
you don't have a variable: '[ || test || ]' is a string. you need this one '[' || test || ']'
Oracle regexp_like uses single quotes to delimit the expression. To use a variable you'll need to isolate the regex metacharacters from the variable, so just concatenate the parts to complete the requirement. Use the 'i' option is to ignore case sensitivity.
Try this:
test varchar(5);
test := '1';
select * from timedetail
where empnum = '013061361'
and tsdate = '1-nov-13'
and regexp_like(errors, test,'i'); --matches any string containing 1
--and regexp_like(errors,'^' || test,'i') --matches any string beginning 1
--and regexp_like(errors, test || '$','i') --matches any string ending 1
--and regexp_like(errors, '^' || test || '$','i') --matches any string exactly 1
