Reverse SQL like not working in Oracle 12c

0
votes

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?

2
oracleoracle12c
You must use concat(concat('%',code), '%') to concatinate three strings. - Marmite Bomber
Oracle's CONCAT can only concatenate two strings. I don't know why the CONCAT function even exists in either DBMS. The SQL standard concatenation operator is ||. - Thorsten Kettner

2 Answers

3
votes

  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?

1
votes

Here you are a simpler test case:

select concat('a', 'b', 'c')
from dual;

ORA-00909: invalid number of arguments

The CONCAT() function expects exactly two arguments. You'll have to nest several calls or use the || operator.

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