How to get missing non numeric IDs in MySQL table

Question

I have a table with ID field format mm_xxxx where xxxx is an hexadecimal value.

So mm_1AF8 is a valid ID.

Now I need to get all the singles IDs that are not in use. I need to search only from mm_0000 to mm_FFFF.

Answer 1

Combine Barmars response with the answer from Robert McKee

SELECT *
FROM yourtable
WHERE id NOT IN (SELECT z1.*
FROM 
(SELECT CONCAT(@prefix, d1.f,d2.f,d3.f,d4.f) as h
FROM (
  SELECT '0' AS f UNION ALL
  SELECT '1' AS f UNION ALL
  SELECT '2' AS f UNION ALL
  SELECT '3' AS f UNION ALL
  SELECT '4' AS f UNION ALL
  SELECT '5' AS f UNION ALL
  SELECT '6' AS f UNION ALL
  SELECT '7' AS f UNION ALL
  SELECT '8' AS f UNION ALL
  SELECT '9' AS f UNION ALL
  SELECT 'A' AS f UNION ALL
  SELECT 'B' AS f UNION ALL
  SELECT 'C' AS f UNION ALL
  SELECT 'D' AS f UNION ALL
  SELECT 'E' AS f UNION ALL
  SELECT 'F' AS f) d1
CROSS JOIN (
  SELECT '0' AS f UNION ALL
  SELECT '1' AS f UNION ALL
  SELECT '2' AS f UNION ALL
  SELECT '3' AS f UNION ALL
  SELECT '4' AS f UNION ALL
  SELECT '5' AS f UNION ALL
  SELECT '6' AS f UNION ALL
  SELECT '7' AS f UNION ALL
  SELECT '8' AS f UNION ALL
  SELECT '9' AS f UNION ALL
  SELECT 'A' AS f UNION ALL
  SELECT 'B' AS f UNION ALL
  SELECT 'C' AS f UNION ALL
  SELECT 'D' AS f UNION ALL
  SELECT 'E' AS f UNION ALL
  SELECT 'F' AS f) d2
CROSS JOIN (
  SELECT '0' AS f UNION ALL
  SELECT '1' AS f UNION ALL
  SELECT '2' AS f UNION ALL
  SELECT '3' AS f UNION ALL
  SELECT '4' AS f UNION ALL
  SELECT '5' AS f UNION ALL
  SELECT '6' AS f UNION ALL
  SELECT '7' AS f UNION ALL
  SELECT '8' AS f UNION ALL
  SELECT '9' AS f UNION ALL
  SELECT 'A' AS f UNION ALL
  SELECT 'B' AS f UNION ALL
  SELECT 'C' AS f UNION ALL
  SELECT 'D' AS f UNION ALL
  SELECT 'E' AS f UNION ALL
  SELECT 'F' AS f) d3
CROSS JOIN (
  SELECT '0' AS f UNION ALL
  SELECT '1' AS f UNION ALL
  SELECT '2' AS f UNION ALL
  SELECT '3' AS f UNION ALL
  SELECT '4' AS f UNION ALL
  SELECT '5' AS f UNION ALL
  SELECT '6' AS f UNION ALL
  SELECT '7' AS f UNION ALL
  SELECT '8' AS f UNION ALL
  SELECT '9' AS f UNION ALL
  SELECT 'A' AS f UNION ALL
  SELECT 'B' AS f UNION ALL
  SELECT 'C' AS f UNION ALL
  SELECT 'D' AS f UNION ALL
  SELECT 'E' AS f UNION ALL
  SELECT 'F' AS f) d4
) z1,(SELECT @prefix := 'mm_') pre);