0

I'd like to use LIKE or REGEX in the SELECT statement of the query (not in the WHERE statement).

If the post_content contains 'base64' I'd like to see 1 in the query output. Is it possible?

SELECT post_content, length(post_content), LIKE('%base64%') AS contained 
FROM `wp_posts`
order by length(post_content) desc
Revious
  • 7,816
  • 31
  • 98
  • 147
  • 1
    The issue is that `LIKE('%base64%')` on its own here makes no sense. You haven't told the database which column you want run the `LIKE` comparison against. (The fact you mentioned post_content in the other columns of the output is irrelevant - as you must know, SQL can output from lots of different columns in a SELECT. Each separate output column must be self-contained and self-describing, so you need to tell it which column to work on for that output. [Mangesh's](https://stackoverflow.com/a/56884663/5947043) second suggestion is the simplest way to do that. – ADyson Jul 04 '19 at 09:28
  • @ADyson: as an alternative would it be possible to use something similar to SELECT 1 from dual WHERE post_content LIKE('%base64%')? - https://stackoverflow.com/questions/73751/what-is-the-dual-table-in-oracle – Revious Jul 04 '19 at 09:37
  • How do you imagine that would work? The dummy `dual` table doesn't contain a field called `post_content`. You have to select from the table where the data is actually being stored. – ADyson Jul 04 '19 at 09:55

1 Answers1

3

You can use the Case statement

SELECT post_content, length(post_content), 
(CASE WHEN post_content  LIKE('%base64%') then "contained " ELSE "not contained" END)  AS contained 
FROM `wp_posts`
order by length(post_content) desc

OR

SELECT post_content, length(post_content), 
 post_content  LIKE('%base64%')   AS contained 
FROM `wp_posts`
order by length(post_content) desc

DEMO

Mangesh Auti
  • 1,123
  • 1
  • 7
  • 12