regexp_like expression not working with variable

Question

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 || ]')

score 0 · Answer 1 · answered May 09 '14 at 15:37

0

you don't have a variable: '[ || test || ]' is a string. you need this one '[' || test || ']'

answered May 09 '14 at 15:37

vav

4,584
2
19
39

It says Test invalid identifier when I try and run – user1345246 May 09 '14 at 15:40
But that is due to how you use your parameters. syntax to use parameters in SQL is different for many tools. Oracle SQL Developer: http://stackoverflow.com/questions/5653423/how-do-i-use-variables-in-oracle-sql-developer . SQL*Plus: http://docs.oracle.com/cd/B19306_01/server.102/b14357/ch5.htm – vav May 09 '14 at 15:48
I'm not following. I'm using Oracle – user1345246 May 09 '14 at 15:53
Oracle is DBMS, it is an engine. Question is how you access it? Two standard tools are SQL*Plus and Oracle SQL Developer. But there are also TOAD, PL/SQL Developer, and others. Parameters in SQL are not part of a standard so everyone free to implement them their own way. – vav May 09 '14 at 16:02
And if this is part of the code in python, c# or another language, you will be using bind variables that are driver specific. – vav May 09 '14 at 16:04
The query should work the same no matter what front end I am using. – user1345246 May 09 '14 at 16:04
Something is wrong the the second argument of regexp_like where test is being used – user1345246 May 09 '14 at 16:05
I'm using sql developer – user1345246 May 09 '14 at 16:06
To get most of this situation I would go with Oracle SQL Developer syntax (as more common between tools). But is not an universal solution. – vav May 09 '14 at 16:12
let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/52407/discussion-between-vav-and-user1345246) – vav May 09 '14 at 16:14

zundarz · Answer 2 · 2019-08-23T13:55:45.407

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

regexp_like expression not working with variable

2 Answers2