Geek asks and answers

Short SQL command to return true if no field contains a string?

I would like to have a short form of SQL command (I use Oracle SQL)

SELECT * from table1 
WHERE field1 not like '%test%' 
AND field2 not like '%test%' 
AND field3 not like '%test%'

Is there any syntax equivalent to this command? I was thinking of something like

SELECT * from table1 
WHERE '%test%' not in (field1, field2, field3)

but this syntax, unfortunately, does not work ?! Thanks so much for all the tips and suggestions.

+3
source share
3 answers

No, this is really impossible.

This transformation, using one of De Morgan 's laws , is a little shorter, but there are only a few differences between the characters:

SELECT *
FROM table1 
WHERE NOT (field1 LIKE '%test%' OR
           field2 LIKE '%test%' OR 
           field3 LIKE '%test%')

, , , , , '%test%' , , '%te_st%':

SELECT *
FROM table1 
WHERE (field1 + '_' + field2 + '_' + field3) NOT LIKE '%test%'

, , , - .

+4

, , - , .

+5
SELECT CASE WHEN (
    (field1 NOT LIKE '%test%') AND
    (field2 NOT LIKE '%test%') AND
    (field3 NOT LIKE '%test%')
THEN true
ELSE false AS truthiness
+1
source

More articles:


All Articles