CODEWITHSUNDEEP
mysqlsql
medk
medk

Reputation: 9549

SQL SELECT WHERE NOT LIKE

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:

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?

Upvotes: 2

Views: 4172

Answers (4)

Nidhi257
Nidhi257

Reputation: 977

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}';

Upvotes: 1

NotCaring
NotCaring

Reputation: 78

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.

Upvotes: 2

Ron van der Heijden
Ron van der Heijden

Reputation: 15080

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%'
)

Upvotes: 2

Gurwinder Singh
Gurwinder Singh

Reputation: 39507

You could you REGEXP:

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

Upvotes: 3

Related Questions