CODEWITHSUNDEEP
mysql
enemetch
enemetch

Reputation: 522

SELECT rows NOT ending in a specific character

I have a table of questions. I need to find rows which have '?' in the question text because of bad character encoding/collation. I need to find all the rows which have '?' but also need to ignore the question marks at the end of the questions. I tried this query but I still get rows with questions marks at end of the question

SELECT * 
FROM `kc_questions` 
WHERE `question` LIKE "%?%" /* WHICH CONTAINS '?' */
AND `question` NOT LIKE "%?"  /* DOES NOT END WITH '?' */

EDIT: phpmyadmin actually tells me there is something wrong with the query: enter image description here

The query however runs successfully returning rows which end with'?'.

Upvotes: 3

Views: 14972

Answers (2)

Dylan Su
Dylan Su

Reputation: 6065

Based on the sample data I tried the following demo and it works as expected.

SQL:

create table kc_questions(question varchar(200));
insert into kc_questions values
('Ex1. ?-particles are harmul for human body. Select True or False.'),
('Ex2. What is your name?');

SELECT question FROM kc_questions;
SELECT * 
FROM `kc_questions` 
WHERE `question` LIKE "%?%" 
AND `question` NOT LIKE "%?";

Output:

mysql> SELECT question FROM kc_questions;
+-------------------------------------------------------------------+
| question                                                          |
+-------------------------------------------------------------------+
| Ex1. ?-particles are harmul for human body. Select True or False. |
| Ex2. What is your name?                                           |
+-------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> SELECT *
    -> FROM `kc_questions`
    -> WHERE `question` LIKE "%?%"
    -> AND `question` NOT LIKE "%?";
+-------------------------------------------------------------------+
| question                                                          |
+-------------------------------------------------------------------+
| Ex1. ?-particles are harmul for human body. Select True or False. |
+-------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> select version();
+-----------+
| version() |
+-----------+
| 5.7.8-rc  |
+-----------+
1 row in set (0.00 sec)

Upvotes: 9

Pit
Pit

Reputation: 1488

You could use a regular expression

SELECT * 
FROM `kc_questions` 
WHERE `question` REGEXP '.*\?.+$'

Basicly you search for questions which contains '?' with at least one character after the '?'

Upvotes: 2

Related Questions