SQL SELECT WHERE NOT LIKE

Question

I have an array:

$codes = array (97121, 97122, 97123, 97180);

The real array is much longer, it contains nearly 130 codes so I think it doesn't have to be a loop.

And I have a mySQL table that has a column VilleAnn (city with postal code) ex:

Sainte-Anne 97180
Sanary-sur-Mer 83110

Using mysqli I have to select where VilleAnn doesn't contain any postal code from the array $codes.

If the VilleAnn is a simple postal code I would say:

SELECT * FROM table WHERE VilleAnn NOT IN(97121, 97122, 97123, 97180)

But in this case it must be something using NOT LIKE "%..."

Any suggestions?

Can't you use an inner query to fetch the postal codes instead of having them as a long list of numbers in a `NOT IN` clause? — N00b Pr0grammer, Feb 23 '17 at 14:06
See https://stackoverflow.com/questions/9416062/sql-like-inside-array — SolarBear, Feb 23 '17 at 14:09

score 3 · Answer 1 · answered Feb 23 '17 at 14:07

3

You could you REGEXP:

SELECT * FROM table WHERE VilleAnn NOT REGEXP '97121|97122|97123|97180|....'

answered Feb 23 '17 at 14:07

Gurwinder Singh

38,557
6
51
76

score 2 · Answer 2 · answered Feb 23 '17 at 14:10

2

Something like this:

SELECT * 
FROM `table` 
WHERE (
    `VilleAnn` NOT LIKE '%97121%' AND 
    `VilleAnn` NOT LIKE '%97122%' AND 
    `VilleAnn` NOT LIKE '%97123%' AND 
    `VilleAnn` NOT LIKE '%97180%'
)

answered Feb 23 '17 at 14:10

Ron van der Heijden

14,803
7
58
82

score 2 · Accepted Answer · answered Feb 23 '17 at 14:11

In condition doesn't work with the wildcard characters. Your best bet is to try and extract the postcode from the original field, in this example

SELECT * FROM table WHERE right(VilleAnn,5) NOT IN ('97121', '97122', '97123', '97180')

I presume real life is more complicated, so this might need to be adjusted to reflect the actual format of the field.

score 1 · Answer 4 · answered Feb 23 '17 at 14:19

1

SELECT * FROM table WHERE VilleAnn NOT REGEXP '[0-9]';

This will help you.

For to check four times occurrences you can use:

SELECT * FROM table WHERE VilleAnn NOT REGEXP '[0-9]{4}';

answered Feb 23 '17 at 14:19

Nidhi257

754
1
5
23

SQL SELECT WHERE NOT LIKE

4 Answers4