Oracle LIKE-wildcard in inner SELECT query

Question

In SQL Server T-SQL I used to use the scenario like this

SELECT .. FROM .. WHERE sometable.eng LIKE (SELECT tmpcolumn FROM tmptable WHERE tmpID = @counter) + '%';

How to pass LIKE (subquery) + '%' in Oracle correcly? Does it actually work for Oracle 11g+ or not?

.. smth LIKE (SELECT .. FROM ..) + '%';

The underscore _ for fixed length doesn't fit my needs, so % only.

See: http://stackoverflow.com/questions/278189/what-is-the-string-concatenation-operator-in-oracle — Barmar, May 02 '14 at 16:57

score 3 · Accepted Answer · answered May 02 '14 at 16:58

3

Oracle uses || for string concatenation, not +. So it should be:

smth LIKE (SELECT .. FROM ..) || '%'

answered May 02 '14 at 16:58

Barmar

741,623
53
500
612

score 0 · Answer 2 · answered May 02 '14 at 17:22

This seems like such an odd formulation. Just as a note, I would write the query as:

SELECT ..
FROM ..
WHERE EXISTS (SELECT 1
              FROM tmptable
              WHERE tmpID = @Counter AND
                    sometable.eng LIKE tmpcolumn || '%'
             );

Putting a subquery between the keyword LIKE and the wildcard makes the query harder to read (at least for me).

Oracle LIKE-wildcard in inner SELECT query

2 Answers2