Coalesce Using LIKE % - Oracle SQL

Asked Jun 09 '16 at 02:05

Active Jun 09 '16 at 02:05

Viewed 542 times

SELECT 
COALESCE (chars.CFWEMAIL,
          chars.CLMEMAIL,
          chars.HLDEMAIL,
          chars.IFREMAIL)                   
                   email
FROM tmp

I am trying to achieve the above without hardcoding the columns into the coalesce statement.

Can I use LIKE % instead?

Coalesce all columns LIKE '%MAIL'

asked Jun 09 '16 at 02:05

Heisenberg

1

You cannot do that in Oracle . . . unless you use dynamic SQL (`execute immediate`). – Gordon Linoff Jun 09 '16 at 02:06
and even with dynamic SQL (server-side or client-side), you have to get the list of columns from somewhere first. – Thilo Jun 09 '16 at 02:08
No. LIKE works on character columns, not column names. – Ken White Jun 09 '16 at 02:09
Related: http://stackoverflow.com/questions/14116292/match-multiple-columns-with-same-value-sql?lq=1 http://stackoverflow.com/questions/4797686/selecting-all-columns-that-start-with-xxx-using-a-wildcard – Thilo Jun 09 '16 at 02:11
You can't do that in any relational database which uses SQL - the `LIKE` operator doesn't operate on column *names*, it operates on column *contents*. – Bob Jarvis - Слава Україні Jun 09 '16 at 02:43

Coalesce Using LIKE % - Oracle SQL

0 Answers0