{"id":67517,"date":"2016-08-31T13:55:03","date_gmt":"2016-08-31T13:55:03","guid":{"rendered":"https:\/\/www.simple-talk.com\/?p=67517"},"modified":"2026-05-08T10:26:31","modified_gmt":"2026-05-08T10:26:31","slug":"the-sql-of-textonyms","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/blogs\/the-sql-of-textonyms\/","title":{"rendered":"Using Computed Columns and Scalar Functions in SQL Server for Text Transformations and Anagrams"},"content":{"rendered":"

SQL Server computed columns let you define a column whose value is derived from other columns via an expression, optionally persisted (stored) and indexable. Combined with a user-defined scalar function, computed columns become a powerful pattern for precomputing text transformations – phonetic encodings, sort keys, normalised forms, identity-independent comparison keys – once per row rather than per query. <\/b><\/p>\n

This article demonstrates the pattern using two worked examples: textonym detection (finding words that share the same phone-keypad digit sequence, like ‘cat’ and ‘act’ both mapping to 228) and anagram detection (finding words that share the same sorted-character string). <\/b><\/p>\n

The same technique applies to case-insensitive search keys, accent-stripped search keys, phonetic match keys (Soundex, Metaphone), and any transformation where the cost of computing matters enough to precompute. Covers the pattern’s performance characteristics, indexability, and when to persist vs compute on read.<\/b><\/p>\n

Textonyms<\/h2>\n

When you typed in words on a mobile phone’s digital pad to ‘text’ someone, there was always the possibility of a particular combination of digits being ambiguous in that it corresponds to more than one word. Either you had \u2018autocorrect\u2019 on, and you chose which word you want, or you had to press repeatedly to cycle through the characters as you typed the word.<\/p>\n

These alternative words for the same string of numbers are called textonyms. What percentage of common words in English would suffer from this sort of ambiguity, what are the strings of numbers that represent a large number of textonyms? What ambiguous text strings match words\u00a0where all the characters are different? What is the largest group of words represented by a texting digit string?<\/p>\n

This a very easy problem to solve in SQL Server. The only irritation is that, although the code is relatively simple, and you can go on to make all sorts of interesting discoveries about possible misunderstandings when texting without checking the screen, the initial setup is not quick, because you are having to transform around 60,000 words. In truth this really doesn’t matter a bit but as we so often have to do other similar transformations on data when inserting it into a table, I thought it would be interesting to explore the process to see where one could speed this up.<\/p>\n

We’ll assume that the digit keys are mapped to letters as follows:<\/p>\n

2 -> ABC\n3 -> DEF\n4 -> GHI\n5 -> JKL\n6 -> MNO\n7 -> PQRS\n8 -> TUV\n9 -> WXYZ<\/pre>\n
Finding Textonyms using SQL<\/h2>\n
Creating the table<\/h3>\n
We’ll assume that you have a table with all the common words in it. If you haven’t got one of these, the data is in a zipped file with a link at the end of the article. One of the Blog posts linked to in the references section has some hints on how to read this into a database table of common words.<\/p>\n
Our first task is to create the table with the 600,000 common words of the English language in it in one column, and the numeric ‘text’ version in the other<\/p>\n
text_Version            word\n---------------------- ---------------------------\n224                    aah\n22738275               aardvark\n227382757              aardvarks\n222287                 abacus\n22228737               abacuses\n2225663                abalone\n22256637               abalones\n2226366                abandon\n222636633              abandoned\n2226366464             abandoning<\/pre>\n
Now. Let’s consider how we convert a string into a text version. Doing a single string is pretty trivial.<\/p>\n
DECLARE @word VARCHAR(2000);DECLARE @word VARCHAR(2000);\nSELECT @word\n    = 'I do not like to get the news, because there has never been an era when so many things were going so right for so many of the wrong persons';\nSELECT @word = REPLACE(@word, The_character, The_digit)\n  FROM (VALUES (2, 'A'), (2, 'B'), (2, 'C'), (3, 'D'), (3, 'E'), (3, 'F'), (4, 'G'), (4, 'H'), (4, 'I'), (5, 'J')\n             , (5, 'K'), (5, 'L'), (6, 'M'), (6, 'N'), (6, 'O'), (7, 'P'), (7, 'Q'), (7, 'R'), (7, 'S'), (8, 'T')\n             , (8, 'U'), (8, 'V'), (9, 'W'), (9, 'X'), (9, 'Y'), (9, 'Z')) mapping (The_digit, The_character);\nSELECT @word AS TheTextVersion;\n\n \n\/* Which gives 4 36 668 5453 86 438 843 6397, 2322873 84373 427 63837 2336 26 372 9436 76 6269 844647 9373 46464 76 74448 367 76 6269 63 843 97664 7377667 *\/\n\n<\/pre>\n
There is, of course, some disguised iteration here, and this is not a simple expression. Experienced database developers will, if nobody else is looking, get a faster transformation by doing many nested REPLACE functions, one for each letter of the alphabet. This turns it into a simple expression that is far more versatile.<\/p>\n
Just to save space, we will imagine for a moment that there were only seven letters in the alphabet. If this were the case it would look like this<\/p>\n
SELECT REPLACE(\n         REPLACE(\n           REPLACE(\n             REPLACE(\n               REPLACE(\n                 REPLACE(\n                   REPLACE(\n                     'Gee. A gaffe. A faded bad dad effaced a facade', 'A',\n                     '2'\n                   ), 'B', '2'\n                 ), 'C', '2'\n               ), 'D', '3'\n             ), 'E', '3'\n           ), 'F', '3'\n         ), 'G', '4'\n       );\n\/* giving 433. 2 42333. 2 32333 223 323 3332233 2 322233.*\/ \n<\/pre>\n
The language has become rather weird when restricted to these seven characters but the full expression for the entire alphabet is tiresome to display in the article. Incidentally, we can find just those words that use these seven characters very simply with this expression.<\/p>\n
Select word from commonwords where word not like '%[^abcdefg]%'--words that just use these characters<\/pre>\n
Now, can this first method translate easily to around 60,000 common words in the English language?<\/p>\n
We convert both of these into a function and fill the table<\/p>\n
IF EXISTS\n  (\n  SELECT * FROM INFORMATION_SCHEMA.ROUTINES\n    WHERE ROUTINE_NAME LIKE 'ConvertToNumbers' AND ROUTINE_SCHEMA = 'dbo'\n)\nSET NOEXEC ON;\nGO\n-- if the routine exists this stub creation stem is parsed but not executed\nCREATE FUNCTION ConvertToNumbers (@word VARCHAR(2000))\nRETURNS VARCHAR(2000)\nAS BEGIN RETURN @word; END;\nGO\n-- the following section will be always executed\nSET NOEXEC OFF;\nGO\nALTER FUNCTION ConvertToNumbers (@word VARCHAR(2000))\nRETURNS VARCHAR(2000)\nAS\n  BEGIN\n  SELECT @Word = REPLACE(@word, The_character, The_digit)\n    FROM\n    (VALUES \n    (2,'A'),(2,'B'),(2,'C'),(3,'D'),(3,'E'),(3,'F'),(4,'G'),(4,'H'),\n\t(4,'I'),(5,'J'),(5,'K'),(5,'L'),(6, 'M'),(6, 'N'),(6, 'O'),(7,'P'),\n\t(7,'Q'),(7,'R'),(7,'S'),(8, 'T'),(8, 'U'),(8, 'V'),(9, 'W'), (9, 'X'),  \n\t(9, 'Y'), (9, 'Z'))mapping(The_digit,The_character)\n  RETURN @Word;\n  END;\nGO\n\nif exists (select * from tempdb.sys.tables where name like '#CommonWordsAndTexting%')\n   Drop table #CommonWordsAndTexting;\ngo\nselect isnull(convert(varchar(22),string),'') as word, \n       convert(varchar(22),dbo.ConvertToNumbers(string)) as Text_Version  into #CommonWordsAndTexting\nfrom commonwords\nalter table #CommonWordsAndTexting add primary key (word)\n<\/pre>\n
Hunting for Textonyms in SQL<\/h3>\n
Once we have our table, the rest is trivial. We can do all sorts of analysis on the results. First we\u2019ll find what percentage of common words of the English language are unique in terms of their text digits.<\/p>\n
\/* now we can find what percentage of text versions map to only one string *\/\nSelect Count(*)*100 \/(Select count(*) from #CommonWordsAndTexting) as PercentageUnique from \n\t(\n\tSelect max(word), Text_Version from #CommonWordsAndTexting\n\t\tgroup by Text_Version having count(*) =1 --51547\n\t)Unique_Textonyms(word,textonym)\n\/*\nPercentageUnique\n----------------\n85\n*\/\n<\/pre>\n
85% is a pretty high proportion which is why \u2018Texting\u2019 became so popular with phone users.<\/p>\n
You can easily find the text digits with the most ambiguity, which represent the most textonyms. The SQL is a bit more complex because there could be more than one in first place.<\/p>\n
SELECT duplicates,\n  Text_Version,\n  STUFF(\n    (\n    SELECT ', ' + word\n      FROM #CommonWordsAndTexting t\n      WHERE t.Text_Version = ProblemTextonyms.Text_Version\n    FOR XML PATH(''), TYPE\n    ).value('.', 'varchar(max)'), 1, 2, ''\n  ) AS Textonyms\n  FROM\n  (\n  SELECT COUNT(*), text_version\n    FROM #CommonWordsAndTexting\n    GROUP BY text_version\n    HAVING COUNT(*) =\n    (\n      SELECT TOP 1 COUNT(*)\n        FROM #CommonWordsAndTexting\n        GROUP BY Text_Version\n        HAVING COUNT(*) > 1\n        ORDER BY COUNT(*) DESC\n    )\n  )ProblemTextonyms(duplicates, Text_Version);\n\/*\nduplicates Text_Version           \n---------- ------------ ------------------------------------------------------------------------------------------\n13          22737       acres, bards, barer, bares, barfs, baser, bases, caper, capes, cards, carer, cares, cases\n*\/\n<\/pre>\n
You can list all the ambiguous strings of text digits if you wish.<\/p>\n
SELECT Duplicates, Text_Version, Textonyms\n  FROM\n  (\n  SELECT Duplicates, Text_Version,\n    STUFF((SELECT ', ' + word\n             FROM #CommonWordsAndTexting t\n             WHERE t.Text_version = ProblemTextonyms.Text_version\n          FOR XML PATH(''), TYPE\n          ).value('.', 'varchar(max)'), 1, 2, ''\n    ) AS Textonyms\n    FROM\n    (SELECT COUNT(*), Text_version\n       FROM #CommonWordsAndTexting\n       GROUP BY Text_version\n       HAVING COUNT(*) > 2\n    ) ProblemTextonyms(Duplicates, Text_Version)\n  UNION ALL\n  SELECT 2, Text_version, MAX(word) + ', ' + MIN(word)\n    FROM #CommonWordsAndTexting\n    GROUP BY Text_version\n    HAVING COUNT(*) = 2\n  ) f\n  ORDER BY Duplicates DESC;\n<\/pre>\n
<\/p>\n
We can find all palindromic text versions for words that aren’t palindromes.<\/p>\n
SELECT word, text_version\n  FROM #CommonWordsAndTexting\n  WHERE text_version = REVERSE(text_version) AND word <> REVERSE(word);\n<\/pre>\n
<\/p>\n
We can find all the duplicates that do not have a single letter in common \u2026<\/p>\n
SELECT text_version, first_word, second_word\n  FROM\n  (SELECT Text_Version, MAX(word), MIN(word)\n     FROM #CommonWordsAndTexting\n     GROUP BY Text_Version\n     HAVING COUNT(*) = 2\n  ) duplicates(text_version, first_word, second_word)\n  WHERE first_word NOT LIKE '%[' + second_word + ']%'\n  ORDER BY LEN(text_version) DESC;\n<\/pre>\n
<\/p>\n
\u2026 or we can find every pair of textonym that have no character in common.<\/p>\n
SELECT Text_Version, one, two\n  FROM\n  (\n  SELECT The_first.Text_Version,\n    CASE WHEN The_first.word > The_Second.word THEN The_first.word ELSE The_Second.word\n    END AS one,\n    CASE WHEN The_first.word < The_Second.word THEN The_first.word ELSE The_Second.word\n    END AS two\n    FROM #CommonWordsAndTexting The_first\n    INNER JOIN #CommonWordsAndTexting The_Second\n      ON The_first.Text_Version = The_Second.Text_Version\n    WHERE The_first.word NOT LIKE '%[' + The_Second.word + ']%'\n  ) NothingLike(Text_Version, one, two)\n  GROUP BY Text_Version, one, two\n  ORDER BY LEN(Text_Version) DESC;\n<\/pre>\n
<\/p>\n
Alternative ways of\u00a0doing data transformations.<\/h3>\n
There is a lot we can do once we have our table. Before we got started with exploring textonyms, we weren\u2019t bothered about performance as it was a one-off process and though it took several seconds, it was of little concern. The same goes for the SQL that explored the textonyms. However, what is the speediest way of creating a table of common words and their text digits? I didn\u2019t like that function: it seemed as if it would be slow. I\u2019m often creating ETL routines for importing text and doing transformations on them so now we have a decent-sized sample, I thought it would be amusing to find out.<\/p>\n
To time an import routine, you really have to time the whole period that it takes from the creation of the table to getting a result. In my case, I needed to time the entire period from start, having nothing but the table of common words, to the point at which we have it confirmed that 85% of them are unique and unambiguous. I tried out five different approaches to the task, but I\u2019ve kept the code separate as a download because it is otherwise a bit distracting. (I actually tried out some other approaches such as a Quirky Update and XML-based approach that were both impossibly slow.)<\/p>\n
The first thing to try out was to take away the function entirely, and do the transformation within the SQL expression that inserted the words into our table.<\/p>\n
Remember that expression that only did the first seven letters of the alphabet? We expand it to do them all. It\u2019s big and it\u2019s ugly, but it reduces the end-to-end time by around 20%. We reckon that we can do better by doing this simple expression in a table-valued function. Well, it is actually about the same time.<\/p>\n
What about moving the logic of the data transformation into the table itself as a calculated column? We can do this easily with a simple expression and by doing so we deduce the time by a half. This is a saving worth pursuing. You can, of course, use a user-defined function in a computed column expression but the drawback to this is that SQL Server chooses a sequential plan rather than the faster parallel plan. Also they are difficult in temporary tables and you have to make them deterministic so that you can then specify constraints on the column or make them persisted. All in all, it is better to keep them to sql scalar expressions if possible. However, scalar functions can be made to work.<\/p>\n
Using scalar functions in computed columns<\/h3>\n
Here is a simple example of this technique<\/p>\n
Imagine that we want to have postcode information extracted from an address. We could use a calculated column to do the work so that the column automatically remains correct after any type of changes to the data.<\/p>\n
We might do this<\/p>\n
\/* extract a UK postcode from an address string, returning the address string *\/\n\/* first we tear down any existing version *\/\nIF EXISTS (SELECT *\n             FROM INFORMATION_SCHEMA.Tables\n             WHERE TABLE_NAME LIKE 'Addresses' AND TABLE_SCHEMA = 'dbo'\n)\nDROP TABLE addresses;\nGO\n\nIF EXISTS\n  (\n  SELECT *\n    FROM INFORMATION_SCHEMA.ROUTINES\n    WHERE ROUTINE_NAME LIKE 'PostcodeFrom'\n      AND ROUTINE_SCHEMA = 'dbo'\n      AND ROUTINE_TYPE = 'FUNCTION'\n)\nDROP FUNCTION dbo.PostcodeFrom;\nGO\n\nCREATE FUNCTION dbo.PostcodeFrom(@Address VARCHAR(200))\nRETURNS VARCHAR(10)\nWITH SCHEMABINDING\nAS\n  BEGIN\n  DECLARE @postcode VARCHAR(10);\n  SELECT TOP 1 @postcode = SUBSTRING(@address, start, Thelength)\n    FROM\n    (\n    SELECT Thelength, PATINDEX([matched], @Address) AS start --there may be more than one\n      FROM\n      (\n      VALUES('%', 0), \n\t    ('%[A-Z][0-9][ ][0-9][A-Z][A-Z]%', 6),\n        ('%[A-Z][A-Z0-9][A-Z0-9][ ][0-9][A-Z][A-Z]%', 7),\n        ('%[A-Z][A-Z][0-9][A-Z0-9][ ][0-9][A-Z][A-Z]%', 8)\n      ) possibilities(\n      matched, Thelength\n      )\n    ) f\n    WHERE start > 0\n    ORDER BY Thelength DESC;\n  RETURN @postcode;\n  END;\nGO\n\nCREATE TABLE Addresses\n  (\n    Address_ID INT IDENTITY PRIMARY KEY,\n    [Address] VARCHAR(200) NOT NULL,\n    Postcode AS dbo.PostcodeFrom(Address) PERSISTED NOT NULL\n);\n\nINSERT INTO Addresses(Address) -- insert some sample data\n  SELECT [address]\n  FROM\n  (\n  VALUES('The Lodge. U Cathedral Avenue. Belfast CN88 9RR UK'),\n    ('76 High St, Molesey, West Molesey KT8 2LY, UK'),\n    ('11 Lordsfield Gardens, Overton, Basingstoke RG25 3EW, UK'),\n    ('2124 Market Way, Gateshead NE11 0RE, UK'),\n    ('2 Nant-Y-Patrick, Saint Asaph LL17 0BN, UK'),\n    ('40 Milner Rd, Heswall, Wirral CH60 5RZ, UK'),\n    ('1 B2082, Tenterden TN30 7LJ, UK'), ('Dean Ln, York YO43 4UN, UK'),\n    ('2 Parkgate Rd, Wallington SM6 0AE, UK'),\n    ('34-40 A474, Pontardawe, Swansea SA8 4SL, UK'),\n    ('21 Greenslade Rd, Walsall WS5 3QH, UK'),\n    ('1 Vine St, Lincoln LN2 5HZ, UK'),\n    ('16B Wrottesley Rd, London NW10 5YL, UK'),\n    ('25 Delamere Rd, Birmingham B28 0EP, UK'),\n    ('1 Monkwell Square, London EC2Y 1WP, UK'),\n    ('28 Main St, Glengarnock, Beith KA14 3AT, UK'),\n    ('53 Northgate St, Devizes SN10 1JJ, UK'),\n    ('35B Crouch Hill, London N4 4AP, UK'),\n    ('3 Blandford Ave, Luton LU2 7AX'),\n    ('387 Daiglen Dr, South Ockendon RM15 5AJ, UK'),\n    ('18 Invertiel Terrace, Kirkcaldy KY1 1TF, UK'),\n    ('92 Cleave Ave, Hayes UB3 4HB, UK'),\n    ('11 Mutley Rd, Plymouth PL3 4SA, UK'),\n    ('2 Butchers Ln, Walton on the Naze CO14 8QU, UK'),\n    ('Unnamed Road, Llangadog SA19 9SS, UK'),\n    ('10 Howards Way, Rustington, Littlehampton BN16 2LT, UK'),\n    ('Lilac Cottage. 903 Lower Acacia Lane. Leeds WP 6AZ UK'),\n    ('The Barn. 52 Cathedral Road. Wakefield RHCI 1JR UK')\n  ) addresses(\n  [address]\n  );\n\nSELECT * FROM Addresses;\n<\/pre>\n
<\/p>\n
We\u2019ve made the function deterministic by using WITH SCHEMABINDING and this has allowed us to persist the calculated data. We could put an index on this column as well.<\/p>\n
Trying out the Technique to find Anagrams<\/h3>\n
We can us the technique to find words that are anagrams of each other. \u00a0From end to end is a ten-second wait, but once you have the table then there there is a lot of \u00a0ways you can explore the results. I was somewhat surprised to find that the longest number of words that are all anagrams of each other is seven.<\/p>\n
-----\nIF EXISTS (SELECT * FROM sys.tables WHERE name LIKE 'CommonWordsAndLetters%')\nDROP TABLE CommonWordsAndLetters;\nGO\n\nIF OBJECT_ID(N'dbo.SortedVersionOfWord') IS NOT NULL\nDROP FUNCTION dbo.SortedVersionOfWord;\nGO\n\nCREATE FUNCTION dbo.SortedVersionOfWord(@anUnsortedword VARCHAR(22))\nRETURNS VARCHAR(24)\nWITH SCHEMABINDING, EXECUTE AS CALLER\nAS\n  BEGIN\n  RETURN\n    (\n    SELECT SUBSTRING(@anUnsortedword, numbers.number, 1) AS TheCharacter\n      FROM\n      (VALUES(1), (2), (3), (4), (5), (6), (7), (8), (9), (10), (11), (12),\n        (13), (14), (15), (16), (17), (18), (19), (20), (21), (22), (23),(24)\n      ) numbers(number)\n      WHERE numbers.number < = LEN(@anUnsortedword)\n      ORDER BY TheCharacter\n    FOR XML PATH(''), TYPE\n    ).value('.', 'varchar(max)');\n  END;\nGO\nCREATE TABLE CommonWordsAndLetters\n  (\n    word VARCHAR(22) NOT NULL,\n    letters AS dbo.SortedVersionOfWord(word) PERSISTED NOT NULL\n);\nALTER TABLE CommonWordsAndLetters\nADD PRIMARY KEY(word);\nINSERT INTO CommonWordsAndLetters SELECT String FROM commonwords;\n\nSELECT f.Anagram_List\n  FROM\n  (\n  SELECT Anagrams.duplicates,\n    STUFF((SELECT ', ' + t.word\n             FROM CommonWordsAndLetters t\n             WHERE t.letters = Anagrams.letters\n          FOR XML PATH(''), TYPE\n          ).value('.', 'varchar(max)'),1,2,'') AS Anagram_List\n    FROM\n    (SELECT COUNT(*) AS duplicates, letters\n       FROM CommonWordsAndLetters\n       GROUP BY letters\n       HAVING COUNT(*) > 2\n    ) Anagrams(duplicates, letters)\n  UNION ALL\n  SELECT 2, MAX(word) + ', ' + MIN(word)\n    FROM CommonWordsAndLetters\n    GROUP BY letters\n    HAVING COUNT(*) = 2\n  ) f\n  ORDER BY f.duplicates DESC;<\/pre>\n
Which will give this…<\/p>\n
\"anagrams\"<\/a><\/p>\n
Conclusion.<\/h2>\n
With any functionality that you put into a database, you have a number of possibilities. Even with this very simple task, importing a table of common words and doing a transformation on it, there were many different ways of doing it. In this case, the use of a computed column turned out to be the best approach but you can\u2019t assume that this is necessarily the general case. If there is a general rule it is that one should avoid duplicating functionality if it can be avoided. However, if code like this goes into a trigger or a computed column, it can be exasperatingly tricky to find in a hurry. The great advantage, though, is that data can be entered via a simple sql expression without any thought to applying the transformation to each row as part of the process. It happens auto-\u2018magically\u2019. My instinct is to place this type of transformation as close as possible to the data.<\/p>\n
References<\/p>\n