CODEWITHSUNDEEP
phpmysqlreplaceunique
jwegner
jwegner

Reputation: 7423

MySQL Replace Value From Each Record

I have a table in my database that looks like this:

=========
| id | code |
=========
| 1 | a |
=========
| 2 | e |
=========
| 3 | r |
=========

and so on. I would like to be able to do a query that will tell me which letters in the alphabet are not in the table.

I was originally thinking of a query like this:

SELECT REPLACE('abcdefghijklmnopqrstuvwxyz', (SELECT code FROM table), '');

and hoping that would output 'defghijklmnopqrstuvwxyz', and I could just explode that into an array of unused characters. Unfortunately, MySQL's REPLACE doesn't allow a table of values to be used.

Any suggestions on how to build this list?

Upvotes: 4

Views: 239

Answers (2)

Joachim Isaksson
Joachim Isaksson

Reputation: 181047

The way I would recommend to do it is to create a table with a row for each letter in the alphabet, that would make it easy to remove the letters with a LEFT JOIN and GROUP_CONCAT at the end.

Lacking that table, you'll have to resort to a hack, building a temporary "alphabet table" using a JOIN between a temporary variable and a table with more rows than the number of letters. For this example, I use INFORMATION_SCHEMA.COLLATIONS.

SELECT GROUP_CONCAT(CHAR(ch) SEPARATOR '') 'missing letters' FROM 
  (SELECT @tmp:=@tmp+1 ch 
   FROM (SELECT @tmp:=96) a, INFORMATION_SCHEMA.COLLATIONS
   WHERE @tmp<122) z
LEFT JOIN TableA ON ch=ORD(TableA.code)
WHERE TableA.code IS NULL;

The nested SELECT builds the alphabet sequence, the LEFT JOIN removes the letters that exist in TableA (your table). GROUP_JOIN concatenates the resulting letters. The result is a string with all letters that don't exist in TableA.

Upvotes: 1

Peter Kiss
Peter Kiss

Reputation: 9329

Build the logic on the server side. Create a query to get all the used letters (distinct), then walk through the result set and clear out the used chars from the possible array/string.

Upvotes: 0

Related Questions