SQL query that extracts a topic keyword from article titles
up vote
1
down vote
favorite
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?
sql google-bigquery
edited yesterday
200_success
127k15149412
asked yesterday
phwt
486
add a comment |
up vote
1
down vote
favorite
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?
sql google-bigquery
edited yesterday
200_success
127k15149412
asked yesterday
phwt
486
1
Also, can you provide sampletitlevalues? I'm curious to see how they're formed, that can help answerers as well.
– 202_accepted
yesterday
Is it intent that he first match wins?
– aventurin
yesterday
@202_accepted I've added sample value intitlecolumn. It's more like an article title.
– phwt
yesterday
@aventurin Yes, the first match wins.
– phwt
yesterday
@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
– 202_accepted
15 hours ago
add a comment |
up vote
1
down vote
favorite
up vote
1
down vote
favorite
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?
sql google-bigquery
edited yesterday
200_success
127k15149412
asked yesterday
phwt
486
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?
sql google-bigquery
sql google-bigquery
edited yesterday
200_success
127k15149412
asked yesterday
phwt
486
edited yesterday
200_success
127k15149412
asked yesterday
phwt
486
edited yesterday
200_success
127k15149412
edited yesterday
200_success
127k15149412
edited yesterday
200_success
127k15149412
127k15149412
asked yesterday
phwt
486
asked yesterday
phwt
486
asked yesterday
phwt
486
486
1
Also, can you provide sampletitlevalues? I'm curious to see how they're formed, that can help answerers as well.
– 202_accepted
yesterday
Is it intent that he first match wins?
– aventurin
yesterday
@202_accepted I've added sample value intitlecolumn. It's more like an article title.
– phwt
yesterday
@aventurin Yes, the first match wins.
– phwt
yesterday
@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
– 202_accepted
15 hours ago
add a comment |
1
Also, can you provide sampletitlevalues? I'm curious to see how they're formed, that can help answerers as well.
– 202_accepted
yesterday
Is it intent that he first match wins?
– aventurin
yesterday
@202_accepted I've added sample value intitlecolumn. It's more like an article title.
– phwt
yesterday
@aventurin Yes, the first match wins.
– phwt
yesterday
@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
– 202_accepted
15 hours ago
1
1
Also, can you provide sample
title values? I'm curious to see how they're formed, that can help answerers as well.– 202_accepted
yesterday
Also, can you provide sample
title values? I'm curious to see how they're formed, that can help answerers as well.– 202_accepted
yesterday
Is it intent that he first match wins?
– aventurin
yesterday
Is it intent that he first match wins?
– aventurin
yesterday
@202_accepted I've added sample value in
title column. It's more like an article title.– phwt
yesterday
@202_accepted I've added sample value in
title column. It's more like an article title.– phwt
yesterday
@aventurin Yes, the first match wins.
– phwt
yesterday
@aventurin Yes, the first match wins.
– phwt
yesterday
@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
– 202_accepted
15 hours ago
@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
– 202_accepted
15 hours ago
add a comment |
active
oldest
votes
active
oldest
votes
active
oldest
votes
active
oldest
votes
active
oldest
votes
draft saved
draft discarded
Thanks for contributing an answer to Code Review Stack Exchange!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
Use MathJax to format equations. MathJax reference.
To learn more, see our tips on writing great answers.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fcodereview.stackexchange.com%2fquestions%2f209008%2fsql-query-that-extracts-a-topic-keyword-from-article-titles%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
1
Also, can you provide sample
titlevalues? I'm curious to see how they're formed, that can help answerers as well.– 202_accepted
yesterday
Is it intent that he first match wins?
– aventurin
yesterday
@202_accepted I've added sample value in
titlecolumn. It's more like an article title.– phwt
yesterday
@aventurin Yes, the first match wins.
– phwt
yesterday
@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
– 202_accepted
15 hours ago