2
\$\begingroup\$

I have a query like this to select the only row with title contains specific programming topics.

SELECT
    posted_date,
CASE
    WHEN LOWER(title) LIKE "%c#%" THEN "c#"
    WHEN LOWER(title) LIKE "%c++%" THEN "c++"
    .
    .
    .
    WHEN LOWER(title) LIKE "%sql%" THEN "sql"
    ELSE ""
END AS single_topics
FROM
    article_data
WHERE
    EXTRACT(YEAR FROM posted_date) = 2018

It's working fine but I wonder that can WHEN in CASE part be shortened in case of a lot of topics to choose?

Sample data in title column (they are like article title).

The fastest way to do x in C++.
Things most people get wrong about JavaScript.
How to send android push notification using cURL.
macOS Mojave - Features, Specifications and more.
What is map, filter and reduce function in Python and how to use them?
\$\endgroup\$
5
  • 1
    \$\begingroup\$ Also, can you provide sample title values? I'm curious to see how they're formed, that can help answerers as well. \$\endgroup\$ Dec 4, 2018 at 17:52
  • \$\begingroup\$ Is it intent that he first match wins? \$\endgroup\$
    – aventurin
    Dec 4, 2018 at 18:08
  • \$\begingroup\$ @202_accepted I've added sample value in title column. It's more like an article title. \$\endgroup\$
    – phwt
    Dec 5, 2018 at 4:05
  • \$\begingroup\$ @aventurin Yes, the first match wins. \$\endgroup\$
    – phwt
    Dec 5, 2018 at 4:05
  • 1
    \$\begingroup\$ @phwt So I would recommend using a Full-Text Search of some sort, with Google BigQuery I'm not sure what that looks like, but this Stack Overflow post looks like it has info on it: Does Google BigQuery support Full-Text search \$\endgroup\$ Dec 5, 2018 at 14:04

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.