Joe Celko's Thinking in Sets: Auxiliary, Temporal, and Virtual Tables in SQL

18.1. Removing Letters in a String

We had previously seen an example of how to remove extra blanks from a string of characters in pure SQL. Consider another version of that kind of problem. Given a string of characters, remove all the redundant duplicate letters from it, in left-to-right order. An example will make this clear: “abcbdabcbcc” would reduce to “abcd” because those four letters appear one or more times to the left of their n-th occurrence (n > 1) in the string of characters.

Let’s create some sample data and expected results.

CREATE TABLE Wordlist (word_key INTEGER NOT NULL PRIMARY KEY, word_txt VARCHAR(25) NOT NULL); INSERT INTO Wordlist VALUES (1, 'aaaaaa'); -- 'a' INSERT INTO Wordlist VALUES (2, 'abababa'); -- 'ab' INSERT INTO ...