Check anagrams using sql server

Question

ACT and CAT are anagrams

I have to Write a function in sql server that takes 2 strings and given a Boolean output that indicates whether the both of them are anagram or not.

This doesnt make sense to do it in sql server,but,it is for learning purpose only

Answer 1

SQL Server is not good at this kind of things, but here you are:

WITH Src AS
(
    SELECT * FROM (VALUES
    ('CAT', 'ACT'),
    ('CAR', 'RAC'),
    ('BUZ', 'BUS'),
    ('FUZZY', 'MUZZY'),
    ('PACK', 'PACKS'),
    ('AA', 'AA'),
    ('ABCDEFG', 'GFEDCBA')) T(W1, W2)
), Numbered AS
(
    SELECT *, ROW_NUMBER() OVER (ORDER BY (SELECT 1)) Num
    FROM Src
), Splitted AS
(
    SELECT Num, W1 Word1, W2 Word2, LEFT(W1, 1) L1, LEFT(W2, 1) L2, SUBSTRING(W1, 2, LEN(W1)) W1, SUBSTRING(W2, 2, LEN(W2)) W2
    FROM Numbered
    UNION ALL
    SELECT Num, Word1, Word2, LEFT(W1, 1) L1, LEFT(W2, 1) L2, SUBSTRING(W1, 2, LEN(W1)) W1, SUBSTRING(W2, 2, LEN(W2)) W2
    FROM Splitted
    WHERE LEN(W1)>0 AND LEN(W2)>0
), SplitOrdered AS
(
    SELECT *,
        ROW_NUMBER() OVER (PARTITION BY Num ORDER BY L1) LNum1,
        ROW_NUMBER() OVER (PARTITION BY Num ORDER BY L2) LNum2
    FROM Splitted
)
SELECT S1.Num, S1.Word1, S1.Word2, CASE WHEN COUNT(*)=LEN(S1.Word1) AND COUNT(*)=LEN(S1.Word2) THEN 1 ELSE 0 END Test
FROM SplitOrdered S1
JOIN SplitOrdered S2 ON S1.L1=S2.L2 AND S1.Num=S2.Num AND S1.LNum1=S2.LNum2
GROUP BY S1.Num, S1.Word1, S1.Word2

And results:

1   CAT     ACT     1
2   CAR     RAC     1
3   BUZ     BUS     0
4   FUZZY   MUZZY   0
5   PACK    PACKS   0
6   AA      AA      1
7   ABCDEFG GFEDCBA 1

Answer 2

First split (T-SQL Split Word into characters) both words into temporary tables. Then perform an outer join and check for nulls.

Edit thanks to George's comment:

1. split (T-SQL Split Word into characters) both words into temporary tables
2. Modify temporary tables or use CTEs to add a column with count(*) with group by letters clause
3. Perform a full outer join on two temporary tables using a letter and it's count in join condition
4. Check for nulls in the output - if there are none, you have an anagram

Answer 3

The first get in my mind:

DECLARE @word1 nvarchar(max) = NULL,
        @word2 nvarchar(max) = 'Test 1',
        @i int = 0, @n int

DECLARE @table TABLE (
    id int,
    letter int
)

SELECT @word1 = ISNULL(LOWER(@word1),''), @word2 = ISNULL(LOWER(@word2),'')

SELECT @n = CASE WHEN LEN(@word1) > LEN(@word2) THEN LEN(@word1) ELSE LEN(@word2) END

WHILE @n > 0
BEGIN
    INSERT INTO @table
    SELECT 1, ASCII(SUBSTRING(@word1,@n,1))
    UNION ALL
    SELECT 2, ASCII(SUBSTRING(@word2,@n,1))
    SET @n=@n-1
END

SELECT CASE WHEN COUNT(*) = 0 THEN 1 ELSE 0 END isAnagram
FROM (
    SELECT id, letter, COUNT(letter) as c
    FROM @table
    WHERE id = 1
    GROUP BY id, letter)as t
FULL OUTER JOIN (
    SELECT id, letter, COUNT(letter) as c
    FROM @table
    WHERE id = 2
    GROUP BY id, letter) as p
    ON  t.letter = p.letter and t.c =p.c
WHERE t.letter is NULL OR p.letter is null

Output:

isAnagram
0

Answer 4

You can also use loops in functions, and they can work fast. I am not able to get any of the of other answers even close to the performance of this function:

CREATE FUNCTION IsAnagram 
(
    @value1 VARCHAR(255)
    , @value2 VARCHAR(255)
) 
RETURNS BIT
BEGIN

    IF(LEN(@value1) != LEN(@value2))
        RETURN 0;

    DECLARE @firstChar VARCHAR(3);

    WHILE (LEN(@value1) > 0)
    BEGIN
        SET @firstChar = CONCAT('%', LEFT(@value1, 1), '%');

        IF(PATINDEX(@firstChar, @value2) > 0)
            SET @value2 = STUFF(@value2, PATINDEX(@firstChar, @value2), 1, '');
        ELSE
            RETURN 0;

        SET @value1 = STUFF(@value1, 1, 1, '');

    END

    RETURN (SELECT IIF(@value2 = '', 1, 0));

END

GO

SELECT dbo.IsAnagram('asd', 'asd')
--1
SELECT dbo.IsAnagram('asd', 'dsa')
--1
SELECT dbo.IsAnagram('assd', 'dsa')
--0
SELECT dbo.IsAnagram('asd', 'dssa')
--0
SELECT dbo.IsAnagram('asd', 'asd')

Answer 5

This is something a numbers table can help with.

Code to create and populate a small numbers table is below.

CREATE TABLE dbo.Numbers
  (
     Number INT PRIMARY KEY
  );

WITH Ten(N) AS 
(
    SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL 
    SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL 
    SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
)   
INSERT INTO dbo.Numbers
SELECT ROW_NUMBER() OVER (ORDER BY @@SPID) AS Number
FROM   Ten T10,
       Ten T100,
       Ten T1000

Once that is in place you can use

SELECT W1,
       W2,
       IsAnagram = CASE
                     WHEN LEN(W1) <> LEN(W2)
                       THEN 0
                     ELSE
                       CASE
                         WHEN EXISTS (SELECT SUBSTRING(W1, Number, 1),
                                             COUNT(*)
                                      FROM   dbo.Numbers
                                      WHERE  Number <= LEN(W1)
                                      GROUP  BY SUBSTRING(W1, Number, 1)
                                      EXCEPT
                                      SELECT SUBSTRING(W2, Number, 1),
                                             COUNT(*)
                                      FROM   dbo.Numbers
                                      WHERE  Number <= LEN(W2)
                                      GROUP  BY SUBSTRING(W2, Number, 1))
                           THEN 0
                         ELSE 1
                       END
                   END 
FROM  (VALUES
        ('CAT', 'ACT'),
        ('CAR', 'RAC'),
        ('BUZ', 'BUS'),
        ('FUZZY', 'MUZZY'),
        ('PACK', 'PACKS'),
        ('AA', 'AA'),
        ('ABCDEFG', 'GFEDCBA')) T(W1, W2)

Or an alternative implementation could be

   IsAnagram = CASE
                 WHEN LEN(W1) <> LEN(W2)
                   THEN 0
                 ELSE
                   CASE
                     WHEN EXISTS (SELECT 1
                                  FROM   dbo.Numbers N
                                         CROSS APPLY (VALUES(1,W1),
                                                            (2,W2)) V(Col, String)
                                  WHERE  N.Number <= LEN(W1)
                                  GROUP  BY SUBSTRING(String, Number, 1)
                                  HAVING COUNT(CASE WHEN Col = 1 THEN 1 END) <> 
                                         COUNT(CASE WHEN Col = 2 THEN 1 END))
                       THEN 0
                     ELSE 1
                   END
               END