oracle select like as column

Question

Hi I need to correct this query, which ends with error:

missing right parenthesis

what's nonsense, problem is probably somewhere else

SELECT name, 
      (name LIKE '%adam%') AS score 
  FROM names 
 ORDER BY score DESC

If is not possible use LIKE in SELECT part, how is possible to list "ADAMS" first ?

Are you sure you have that right? AFAIK `LIKE` is only used in a `WHERE` clause. — , Oct 24 '13 at 09:15
yes, I use MySQL typing style, that's didn't work in Oracle. Question is how correct query to be functional — user5332, Oct 24 '13 at 09:20

score 2 · Accepted Answer · answered Oct 24 '13 at 09:29

2

try this:

SELECT name, 
       (CASE WHEN (name LIKE '%adam%') THEN 1 ELSE 0 END) AS score
FROM names 
ORDER BY score DESC

answered Oct 24 '13 at 09:29

slavoo

score 0 · Answer 2 · edited May 23 '17 at 10:25

0

Oracle SQL doesn't have a boolean datatype, so selecting a boolean value is illegal.

edited May 23 '17 at 10:25

Community

answered Oct 24 '13 at 09:17

Klas Lindbäck

score 0 · Answer 3 · answered Oct 24 '13 at 10:19

0

You can also write a SQL in the order by by replacing the score column

answered Oct 24 '13 at 10:19

Sunil Khatri

3 Answers3