Checking for string permutation

A permutation is a change of places. Thus, ‘lolhe’ is a permuted ‘hello’ (commonly referred to as ‘scrambled text’).

I wish to present an SQL solution for checking if two strings are permutations of the same text.

About permutations

So, if ‘lolhe’ is a permutation of ‘hello’, then ‘hello’ is a permutation of ‘lolhe’, as well; and both are permutations of ‘elloh’. The REVERSE() of a text is an example of permutation. Mathematically, string permutation is an equivalence relation, and divides all strings to equivalence classes.

Use cases

• We may be interested in permutations when a user chooses a password. We may disallow a password which is identical to the login name; but we may also disallow upper-lower-case-only transformations of the text. We may still disallow a permutation of the text.
• On a slightly different scale, the two queries: SELECT * FROM City WHERE id IN (5, 21, 13) and SELECT * FROM City WHERE id IN (13, 5, 21) are identical. Here, the permutation is not with string characters, but with string tokens. While the solution discussed is targeted at string characters, it can be easily converted to work with string tokens.

Checking for permutation

The solution I’m suggesting checks for permutation between 2 strings by permuting both to a third, normal form. The two string are permutations of each other if both have the same normal form.

What exactly is a normal form? Well, anything you like, really, as long as it’s deterministic and works the same for all elements in the equivalence class (mathematically speaking, this was a really bad definition, I know).

Enough of mathematical notions: on the practical side, I’ll normalize ‘CAB’ to ‘ABC’, and ‘DOG’ to ‘DGO’. Can you see what normalization means here? I’m merely rearranging the characters in alphabetical order. This rearrangement is in itself a permutation of the string (hence, one last mathematical statement, it can be seen as a representative of the equivalence class).

So, to see if two strings are permutations of each other, we rearrange both in alphabetical order, and see if we got the same text. ‘hello’ and ‘lolhe’ both normalize to ‘ehllo’, hence are permutations.

Normalizing the text

Down to business: how do we normalize a text using SQL? Well, once again, string walking and string unwalking to the rescue. The trick is to break the string apart (to distinct characters), then re-combine the characters, but instead of in original order, do that in ascending order.

Breaking of the word ‘hello’ is as follows:

SELECT
 SUBSTRING(s, value+1, 1) AS c
FROM
 tinyint_asc, (SELECT 'hello' AS s) sel1
WHERE
 value < char_length(s);

Recombining it in ascending character order is as follows:

SELECT GROUP_CONCAT(c ORDER BY c SEPARATOR '') AS normalized FROM
 (
 SELECT
   SUBSTRING(s, value+1, 1) AS c
 FROM
   tinyint_asc, (SELECT 'hello' AS s) sel1
 WHERE
   value < char_length(s)
 ) walked_string_select;
+------------+
| normalized |
+------------+
| ehllo      |
+------------+
1 row in set (0.00 sec)

We can write a stored function to do that more conveniently (and, while at it, also normalize character case):

DELIMITER $$

DROP FUNCTION IF EXISTS `normalize_text`$$
CREATE FUNCTION `normalize_text` (input_text VARCHAR(255) CHARSET utf8) RETURNS VARCHAR(255) CHARSET utf8
DETERMINISTIC
READS SQL DATA
BEGIN
 SELECT GROUP_CONCAT(c ORDER BY c SEPARATOR '') INTO @normalized FROM
 (
 SELECT
   SUBSTRING(s, value+1, 1) AS c
 FROM
   tinyint_asc, (SELECT input_text AS s) sel1
 WHERE
   value < char_length(s)
 ) walked_string_select;
 RETURN LOWER(@normalized);
END$$

DELIMITER ;

And use like this:

mysql> SELECT normalize_text('Smith');
+-------------------------+
| normalize_text('Smith') |
+-------------------------+
| himst                   |
+-------------------------+
1 row in set (0.00 sec)

mysql> SELECT normalize_text('Smith') = normalize_text('Thims');
+---------------------------------------------------+
| normalize_text('Smith') = normalize_text('Thims') |
+---------------------------------------------------+
|                                                 1 |
+---------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT normalize_text('Smith') = normalize_text('Sniff');
+---------------------------------------------------+
| normalize_text('Smith') = normalize_text('Sniff') |
+---------------------------------------------------+
|                                                 0 |
+---------------------------------------------------+
1 row in set (0.00 sec)

To compare tokenized strings (like ‘21,5,13’ vs. ‘5,13,21’), we can use the SUBSTRING_INDEX() function to break the string apart, instead of SUBSTRING().