Search code examples
oracle-databaseoracle12c

Reverse SQL like not working in Oracle 12c

I need to do a reverse like query in Oracle 12c. While it works fine with H2, it fails in Oracle12c.

Table: 

CREATE TABLE t
    ("id" int, "code" varchar(100))
;

INSERT ALL 
    INTO t ("id", "code")
         VALUES (1, 'london')
    INTO t ("id", "code")
         VALUES (2, 'amsterdam')
    INTO t ("id", "code")
         VALUES (3, 'Oslo')
    INTO t ("id", "code")
         VALUES (4, 'Brussels')
SELECT * FROM dual
;

Query:

 select * from t where 'london south' like concat('%',code, '%');

It gives error: ORA-00909: invalid number of arguments

How should i query it get (1, london) as the result?

Solution

    1. Do NOT use double quotes around lower case column names in your DDL unless you really want to force Oracle to store the column names in lower case, which also means you need to use double quotes in your query:

      select *  from t where 'london south' like '%'||"code"||'%' ;

      got the value london as the single row of output.

    2. Why use the concat function when using || is more flexible?