Skip to content

# Checking for string permutation

MySQL Math SQL January 20, 2010February 4, 2010

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().

- Twitter
- LinkedIn
- WhatsApp
- Email
- Facebook
- Print

###
2
Leave a Reply

2 Comment threads
0 Thread replies
0 Followers
Most reacted comment
Hottest comment thread
1 Comment authors
Recent comment authors

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Subscribe
Notify of
SQL: selecting top N records per group | code.openark.org

[…] walking. Examples for string walking (described in the excellent SQL Cookbook) can be found here, here and here. We’ll be using a numbers table: a simple table which lists ascending integer […]

Guest
Noe Dadon

A solution made with C# : static bool checkPerm(string n1,string n2) { if(n1.Length != n2.Length){ return false; } char[] res1 = n1.ToCharArray(); char[] res2 = n2.ToCharArray(); Array.Sort(res1); Array.Sort(res2); string s1 = new string(res1);string s2 = new string(res2); if(!s1.Equals(s2)){ return false; } return true; }

wpDiscuz
Send to Email Address Your Name Your Email Address Cancel
Post was not sent - check your email addresses!
Email check failed, please try again
Sorry, your blog cannot share posts by email.