CODEWITHSUNDEEP
sqlt-sqloracle11g
at-hex
at-hex

Reputation: 3130

Oracle LIKE-wildcard in inner SELECT query

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.

Upvotes: 0

Views: 2083

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1271111

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).

Upvotes: 0

Barmar
Barmar

Reputation: 782653

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

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

Upvotes: 3

Related Questions