Styjun

How many wives did king shaul have

Solving an equation with constraints

Why were 5.25" floppy drives cheaper than 8"?

Knowledge-based authentication using Domain-driven Design in C#

How exploitable/balanced is this homebrew spell: Spell Permanency?

How do I exit BASH while loop using modulus operator?

In the UK, is it possible to get a referendum by a court decision?

How to install cross-compiler on Ubuntu 18.04?

My ex-girlfriend uses my Apple ID to log in to her iPad. Do I have to give her my Apple ID password to reset it?

Could the museum Saturn V's be refitted for one more flight?

Mathematica command that allows it to read my intentions

How to stretch the corners of this image so that it looks like a perfect rectangle?

Is there a hemisphere-neutral way of specifying a season?

Why do I get negative height?

Is this draw by repetition?

Can compressed videos be decoded back to their uncompresed original format?

Why is it a bad idea to hire a hitman to eliminate most corrupt politicians?

How can saying a song's name be a copyright violation?

How to Prove P(a) → ∀x(P(x) ∨ ¬(x = a)) using Natural Deduction

files created then deleted at every second in tmp directory

how do we prove that a sum of two periods is still a period?

How does a dynamic QR code work?

my venezuela girlfriend wants to travel the USA where i live.what does she need to do and how expensive will it become or how difficult?

Do Iron Man suits sport waste management systems?

MySQL- CASE & REGEXP: How to assign reviews to an “other” column?

negate regex pattern in mysqlHow do you set a default value for a MySQL Datetime column?How to find all the tables in MySQL with specific column names in them?How to output MySQL query results in CSV format?How do I connect to a MySQL Database in Python?How do I specify unique constraint for multiple columns in MySQL?How to get a list of MySQL user accountsHow to 'insert if not exists' in MySQL?How to reset AUTO_INCREMENT in MySQL?How to get the sizes of the tables of a MySQL database?How to import an SQL file using the command line in MySQL?

1

I am using CASE statements with REGEXP to assign reviews into different buckets of topics. Although REGEXP is doing a darn good job at that, there is always some text that may not get assigned to any. So I need an "Other" column. How do I modify the query below to achieve that?

SELECT 
 review, 
 CASE 
 WHEN review REGEXP 'relevant keywords' 
 AND review REGEXP 'additional keywords if necessary' 
 THEN 1 
 ELSE 0 
 END AS 'Cleanliness',
 CASE 
 WHEN review REGEXP 'relevant keywords' 
 AND review REGEXP 'additional keywords if necessary' 
 THEN 1 
 ELSE 0 
 END AS 'Food',
 CASE 
 WHEN review REGEXP 'relevant keywords' 
 AND review REGEXP 'additional keywords if necessary' 
 THEN 1 
 ELSE 0 
 END AS 'Wifi' 
FROM review_table;

My CASE statements have multiple AND conditions so adding another CASE statement with a NOT REGEXP didn't seem like a neat option. Note that a review can belong to multiple topics.

The end result will be a view that should look like this:

mysql sql regex case

share|improve this question

edited Mar 21 at 20:49

GMB

20.8k51028

asked Mar 21 at 20:33

Rajat Sharma SubediRajat Sharma Subedi

124

add a comment | 

1

I am using CASE statements with REGEXP to assign reviews into different buckets of topics. Although REGEXP is doing a darn good job at that, there is always some text that may not get assigned to any. So I need an "Other" column. How do I modify the query below to achieve that?

SELECT 
 review, 
 CASE 
 WHEN review REGEXP 'relevant keywords' 
 AND review REGEXP 'additional keywords if necessary' 
 THEN 1 
 ELSE 0 
 END AS 'Cleanliness',
 CASE 
 WHEN review REGEXP 'relevant keywords' 
 AND review REGEXP 'additional keywords if necessary' 
 THEN 1 
 ELSE 0 
 END AS 'Food',
 CASE 
 WHEN review REGEXP 'relevant keywords' 
 AND review REGEXP 'additional keywords if necessary' 
 THEN 1 
 ELSE 0 
 END AS 'Wifi' 
FROM review_table;

My CASE statements have multiple AND conditions so adding another CASE statement with a NOT REGEXP didn't seem like a neat option. Note that a review can belong to multiple topics.

The end result will be a view that should look like this:

mysql sql regex case

share|improve this question

edited Mar 21 at 20:49

GMB

20.8k51028

asked Mar 21 at 20:33

Rajat Sharma SubediRajat Sharma Subedi

124

add a comment | 

I am using CASE statements with REGEXP to assign reviews into different buckets of topics. Although REGEXP is doing a darn good job at that, there is always some text that may not get assigned to any. So I need an "Other" column. How do I modify the query below to achieve that?

SELECT 
 review, 
 CASE 
 WHEN review REGEXP 'relevant keywords' 
 AND review REGEXP 'additional keywords if necessary' 
 THEN 1 
 ELSE 0 
 END AS 'Cleanliness',
 CASE 
 WHEN review REGEXP 'relevant keywords' 
 AND review REGEXP 'additional keywords if necessary' 
 THEN 1 
 ELSE 0 
 END AS 'Food',
 CASE 
 WHEN review REGEXP 'relevant keywords' 
 AND review REGEXP 'additional keywords if necessary' 
 THEN 1 
 ELSE 0 
 END AS 'Wifi' 
FROM review_table;

My CASE statements have multiple AND conditions so adding another CASE statement with a NOT REGEXP didn't seem like a neat option. Note that a review can belong to multiple topics.

The end result will be a view that should look like this:

mysql sql regex case

share|improve this question

edited Mar 21 at 20:49

GMB

20.8k51028

asked Mar 21 at 20:33

Rajat Sharma SubediRajat Sharma Subedi

124

SELECT 
 review, 
 CASE 
 WHEN review REGEXP 'relevant keywords' 
 AND review REGEXP 'additional keywords if necessary' 
 THEN 1 
 ELSE 0 
 END AS 'Cleanliness',
 CASE 
 WHEN review REGEXP 'relevant keywords' 
 AND review REGEXP 'additional keywords if necessary' 
 THEN 1 
 ELSE 0 
 END AS 'Food',
 CASE 
 WHEN review REGEXP 'relevant keywords' 
 AND review REGEXP 'additional keywords if necessary' 
 THEN 1 
 ELSE 0 
 END AS 'Wifi' 
FROM review_table;

share|improve this question

edited Mar 21 at 20:49

GMB

20.8k51028

asked Mar 21 at 20:33

Rajat Sharma SubediRajat Sharma Subedi

124

share|improve this question

edited Mar 21 at 20:49

GMB

20.8k51028

asked Mar 21 at 20:33

Rajat Sharma SubediRajat Sharma Subedi

124

edited Mar 21 at 20:49

GMB

20.8k51028

edited Mar 21 at 20:49

GMB

20.8k51028

asked Mar 21 at 20:33

Rajat Sharma SubediRajat Sharma Subedi

124

asked Mar 21 at 20:33

Rajat Sharma SubediRajat Sharma Subedi

124

2 Answers
2

active

oldest

votes

0

One solution would be create anoter REGEXP expression that represents the negation of all other expressions. But that can quickly become tedious to maintain.

Another option is to just wrap the query and analyze the results in the outer query to generate the additional column. This should be as simple as:

SELECT x.*, (Cleanliness + Food + Wifi = 0) AS Other
FROM (
 --- original query
) x

---

Tip: in MySQL, the return value of a condition expression is 1 on success and 0 on failure. This means that this expression:

CASE 
 WHEN review REGEXP 'relevant keywords' 
 AND review REGEXP 'additional keywords if necessary' 
 THEN 1 
 ELSE 0 
END AS 'Cleanliness'

Can also be written:

(
 review REGEXP 'relevant keywords' 
 AND review REGEXP 'additional keywords if necessary' 
) AS 'Cleanliness'

share|improve this answer

answered Mar 21 at 20:41

GMBGMB

20.8k51028

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  I went with the second option and it worked. Thanks for the tip!
  
  – Rajat Sharma Subedi
  Mar 22 at 19:45
  
  

  
  
  
  

add a comment | 

0

I think we can use the NOT(expression) command

 CASE 
 WHEN review NOT (REGEXP 'relevant keywords' 
 AND review REGEXP 'additional keywords if necessary' )
 THEN 1 
 ELSE 0 
 END AS 'Irrelevant' 

Reference: https://dev.mysql.com/doc/refman/5.7/en/regexp.html

Related: negate regex pattern in mysql

share|improve this answer

answered Mar 22 at 19:31

S-eagleS-eagle

213

add a comment | 

Your Answer

StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");

StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);

else
createEditor();

);

function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);



);

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

Name

Email

Required, but never shown

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55288832%2fmysql-case-regexp-how-to-assign-reviews-to-an-other-column%23new-answer', 'question_page');

);

Post as a guest

Name

Email

Required, but never shown

0

One solution would be create anoter REGEXP expression that represents the negation of all other expressions. But that can quickly become tedious to maintain.

Another option is to just wrap the query and analyze the results in the outer query to generate the additional column. This should be as simple as:

SELECT x.*, (Cleanliness + Food + Wifi = 0) AS Other
FROM (
 --- original query
) x

---

Tip: in MySQL, the return value of a condition expression is 1 on success and 0 on failure. This means that this expression:

CASE 
 WHEN review REGEXP 'relevant keywords' 
 AND review REGEXP 'additional keywords if necessary' 
 THEN 1 
 ELSE 0 
END AS 'Cleanliness'

Can also be written:

(
 review REGEXP 'relevant keywords' 
 AND review REGEXP 'additional keywords if necessary' 
) AS 'Cleanliness'

share|improve this answer

answered Mar 21 at 20:41

GMBGMB

20.8k51028

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  I went with the second option and it worked. Thanks for the tip!
  
  – Rajat Sharma Subedi
  Mar 22 at 19:45
  
  

  
  
  
  

add a comment | 

0

One solution would be create anoter REGEXP expression that represents the negation of all other expressions. But that can quickly become tedious to maintain.

Another option is to just wrap the query and analyze the results in the outer query to generate the additional column. This should be as simple as:

SELECT x.*, (Cleanliness + Food + Wifi = 0) AS Other
FROM (
 --- original query
) x

---

Tip: in MySQL, the return value of a condition expression is 1 on success and 0 on failure. This means that this expression:

CASE 
 WHEN review REGEXP 'relevant keywords' 
 AND review REGEXP 'additional keywords if necessary' 
 THEN 1 
 ELSE 0 
END AS 'Cleanliness'

Can also be written:

(
 review REGEXP 'relevant keywords' 
 AND review REGEXP 'additional keywords if necessary' 
) AS 'Cleanliness'

share|improve this answer

answered Mar 21 at 20:41

GMBGMB

20.8k51028

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  I went with the second option and it worked. Thanks for the tip!
  
  – Rajat Sharma Subedi
  Mar 22 at 19:45
  
  

  
  
  
  

add a comment | 

One solution would be create anoter REGEXP expression that represents the negation of all other expressions. But that can quickly become tedious to maintain.

Another option is to just wrap the query and analyze the results in the outer query to generate the additional column. This should be as simple as:

SELECT x.*, (Cleanliness + Food + Wifi = 0) AS Other
FROM (
 --- original query
) x

---

Tip: in MySQL, the return value of a condition expression is 1 on success and 0 on failure. This means that this expression:

CASE 
 WHEN review REGEXP 'relevant keywords' 
 AND review REGEXP 'additional keywords if necessary' 
 THEN 1 
 ELSE 0 
END AS 'Cleanliness'

Can also be written:

(
 review REGEXP 'relevant keywords' 
 AND review REGEXP 'additional keywords if necessary' 
) AS 'Cleanliness'

share|improve this answer

answered Mar 21 at 20:41

GMBGMB

20.8k51028

SELECT x.*, (Cleanliness + Food + Wifi = 0) AS Other
FROM (
 --- original query
) x

CASE 
 WHEN review REGEXP 'relevant keywords' 
 AND review REGEXP 'additional keywords if necessary' 
 THEN 1 
 ELSE 0 
END AS 'Cleanliness'

(
 review REGEXP 'relevant keywords' 
 AND review REGEXP 'additional keywords if necessary' 
) AS 'Cleanliness'

share|improve this answer

answered Mar 21 at 20:41

GMBGMB

20.8k51028

answered Mar 21 at 20:41

GMBGMB

20.8k51028

answered Mar 21 at 20:41

GMBGMB

20.8k51028

0

I think we can use the NOT(expression) command

 CASE 
 WHEN review NOT (REGEXP 'relevant keywords' 
 AND review REGEXP 'additional keywords if necessary' )
 THEN 1 
 ELSE 0 
 END AS 'Irrelevant' 

Reference: https://dev.mysql.com/doc/refman/5.7/en/regexp.html

Related: negate regex pattern in mysql

share|improve this answer

answered Mar 22 at 19:31

S-eagleS-eagle

213

add a comment | 

0

I think we can use the NOT(expression) command

 CASE 
 WHEN review NOT (REGEXP 'relevant keywords' 
 AND review REGEXP 'additional keywords if necessary' )
 THEN 1 
 ELSE 0 
 END AS 'Irrelevant' 

Reference: https://dev.mysql.com/doc/refman/5.7/en/regexp.html

Related: negate regex pattern in mysql

share|improve this answer

answered Mar 22 at 19:31

S-eagleS-eagle

213

add a comment | 

I think we can use the NOT(expression) command

 CASE 
 WHEN review NOT (REGEXP 'relevant keywords' 
 AND review REGEXP 'additional keywords if necessary' )
 THEN 1 
 ELSE 0 
 END AS 'Irrelevant' 

Reference: https://dev.mysql.com/doc/refman/5.7/en/regexp.html

Related: negate regex pattern in mysql

share|improve this answer

answered Mar 22 at 19:31

S-eagleS-eagle

213

 CASE 
 WHEN review NOT (REGEXP 'relevant keywords' 
 AND review REGEXP 'additional keywords if necessary' )
 THEN 1 
 ELSE 0 
 END AS 'Irrelevant' 

share|improve this answer

answered Mar 22 at 19:31

S-eagleS-eagle

213

answered Mar 22 at 19:31

S-eagleS-eagle

213

answered Mar 22 at 19:31

S-eagleS-eagle

213