In my last blog entry, ‘Eggy Peggy’, I gave an example of how one might replace into text from an entire table in one SQL command.
As well as producing Eggy Peggy language, the technique has a variety of real uses. Just the other day, I had to use the technique for converting a whole range of eight-bit characters in a batch of text files into HTML entities for a website. Actually, the technique turns out to be pretty poor at Eggy Peggy language, because code languages such as Obenglobish, Arpy-Darpy and Ubby-Dubby actually insert their prefixes to vowels either singly or in combination, as was soon pointed out to me in comments.
Obenglobish, for example, inserts the characters ‘ob’ before every vowel sound. This means that the word ‘English ‘ becomes ‘Obenglobish’, and ‘said’ becomes ‘sobaid’, not ‘sobaobid’ (see: the entry on Ubbi_dubbi in the Wikipedia) I ought to emphasise that these encoding schemes are designed to be spoken. They seem pretty silly on the written page but are astonishingly effective when used in conversation. I would recommend it highly when speaking of intimate things on a mobile phone in a packed railway carriage or subway, just so long as nobody has trained their ear to interpret the same coded speech.
Another problem is the capitalisation of words, which I hadn’t tackled. If one prefixes a vowel that is a capital letter the prefix must start with a capital letter.
I thought I ought to make amends for my carelessness by introducing a new encoder which is designed to encode Ubby-Dubby, Obenglobish, Eggy-Peggy, Arpy-Darpy and any other similar scheme you care to invent. It uses patindex and stuff, the two wonderful workhorses of Transact SQL string-manipulation, to work its magic. The Patindex parameter searches first for vowels, and then ‘not-vowels’ to scan through the string for suitable substrings to manipulate.
Can anyone do a suitable decoder to match?
Here is the general encoder for Eggy Peggy, Obenglobish,
UbbyDubby ArpyDarpy Encoder
IF OBJECT_ID('ufsCeggode', 'FN') IS NOT NULL
DROP FUNCTION ufsCeggode;
CREATE FUNCTION dbo.ufsCeggode
@Prefix VARCHAR(10) = 'egg' --'ub' for UbbyDubby or 'ob' for
--Obenglobish, arp for ArpyDarpy
DECLARE @Pos INT, --index of current search
@WhereWeAre INT, --index into string so far
@PrefixLength INT, @next INT; --the
DECLARE @ucasePrefix VARCHAR(10), @FirstVowel CHAR(1), @Vowels VARCHAR(10);
IF @string IS NULL OR @Prefix IS NULL
IF LEN(@Prefix) = 0
IF LEN(@string) = 0
@Pos = 8000, @WhereWeAre = 1,
@ucasePrefix = STUFF(@Prefix, 1,1,UPPER(SUBSTRING(@Prefix, 1, 1))),
@PrefixLength = LEN(@Prefix), @Vowels = 'aeiou';
WHILE @Pos > 0
SELECT @Pos =
PATINDEX('%[' + @Vowels + ']%',
SUBSTRING(@string, @WhereWeAre, 8000)
IF @Pos > 0
SELECT @FirstVowel = SUBSTRING(@string, @WhereWeAre + @Pos - 1, 1);
SELECT @string =
@string, @Pos + @WhereWeAre - 1, 1,
CASE WHEN ASCII(@FirstVowel) <> ASCII(LOWER(@FirstVowel)) THEN
END + LOWER(@FirstVowel)
SELECT @next = @WhereWeAre + @Pos + @PrefixLength - 1;
SELECT @WhereWeAre =
+ PATINDEX('%[^' + @Vowels + ']%',
SUBSTRING(@string, @next, 8000)