Why is my `NOT LIKE` sql request returning values that actually are LIKE?

I am trying to retrieve results that only have characters or characters and numbers in a column, not numbers without characters. There are two types of location results: one that would be integers only, and another that would be characters w/ the possibility of integers, such as 'Refrigerator' or 'Pinpad-7'. I am trying to run a SELECT statement that leaves out such items that are only numbers.

SELECT *, 
  SUM(quantity) OVER (PARTITION BY partName) AS pCount 
  FROM activepartslist 
  INNER JOIN masterpartslist 
  ON activepartslist.mpID = masterpartslist.mpID 
  WHERE location != 'whouse' 
  AND location NOT LIKE '%[0-9]%' 
  ORDER BY partName, serialNumber;

I would have thought that the line location not like '%[0-9]%' would not include results where location would equal to '5601' or '52000987', but would include results where location could be equal to items such as 'Pinpad-7' or 'Refrigerator'.

I've tried adding the '^' to the not like statement, but no change. What is being returned is all the records: 'Pinpad-7', '5601', 'Cooker58', 'Refrigerator', '52000987', etc.... Everything except 'whouse', at least that works. I've tried changing it to AND location LIKE '%[a-zA-Z]%'`, with and without the '^', but that returns nothing for both.

Am I missing something, or did I phrase it incorrectly? Or does mariaDB not take some of the same commands?

Solution

MariaDB's LIKE operator does not support regular expression syntax. For that, we need to use the REGEXP operator:

NOT location REGEXP '^[0-9]+$'

The regex pattern ^[0-9]+$ matches locations which are all numbers. We then retain only locations which are not all numbers.

Another way to phrase this would be to match any location which contains at least one non number:

location REGEXP '[^0-9]'