SQL filter out string that matches condition and with specific exception to pass

97
February 24, 2021, at 11:40 PM

Sample data

| data_col            | 
| --------------------| 
| Realdata            |
| Firsttest           | 
| testSecond          | 
| onetesttwo          | 
| exceptiontestunique | 
| legitdata           | 

I wanted to filter out all the data that contains test with the exception of one

My expected return result is:

Realdata
exceptiontestunique
legitdata

I can code this in PHP like this.

if (strpos($data, 'test') === false || $data === 'exceptiontestunique') return $data;

Or in JavaScript like this

if (data.indexOf('test') === -1 || data === 'exceptiontestunique') {
  return data;
}

But I need it in SQL

WHERE data_col NOT LIKE '%test%'
???????

const datas = ['realdata', 'Firsttest', 'testSecond', 'onetesttwo', 'exceptiontestunique', 'legitdata'];
const filtered = datas.filter(data => {
  if (data.indexOf('test') === -1 || data === 'exceptiontestunique') {
    return data;
  }
})
console.log(filtered);

Answer 1

You could use union all:

SELECT t.*
FROM T
WHERE data_col NOT LIKE '%test%'
UNION ALL
(SELECT t.*
 FROM T
 WHERE data_col LIKE '%test%'
 LIMIT 1
);

Usually, LIMIT is used with ORDER BY, but you don't seem to care which "test" you get.

Answer 2

I can see that in your PHP/Js code you're defining two condition; any value that have 'test' in them but not exceptiontestunique. With a specific condition like that, you can achieve with OR. Example:

SELECT * FROM table1
WHERE (data_col NOT LIKE '%test%' 
            OR data_col='exceptiontestunique');

Demo Fiddle

READ ALSO
When I search a patient name that has another status while the “pending” checkbox is still checked I still get the search results for other status

When I search a patient name that has another status while the “pending” checkbox is still checked I still get the search results for other status

I have a table and the table has a column called statusBasically, this status can be in progress, pending or dispensed

116
Use Azure Active Directory for authentication with MySQL in PHP Application

Use Azure Active Directory for authentication with MySQL in PHP Application

I have been searching all over the place but everything I see about this concerns implementing JWT token authentication for a REST API written in PHP which isn't the question I need answeredI'm looking to understand something a little simpler

106
Across the network Communication in Python

Across the network Communication in Python

I want to build a simple chat app which can communicate between server and client not on the same network but on the different networkI don't want to use socket

106
Why react CLI does not installing template as typescript?

Why react CLI does not installing template as typescript?

After running npm init react-app new-app --template typescript it simply creates a javascript template project, not typescriptI want to create a typescript project using CLI

109