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?

SET QUOTED_IDENTIFIER ON 

GO

SET ANSI_NULLS ON 

GO



/*

Here is the general encoder for Eggy Peggy, Obenglobish,
UbbyDubby ArpyDarpy Encoder

*/

ALTER  FUNCTION [dbo].[ufsCeggode] 

( 

@string VARCHAR(8000),

@Prefix VARCHAR(10)='egg'--'ub' for UbbyDubby or 'ob' for

--Obenglobish, arp for ArpyDarpy

) 

RETURNS VARCHAR(8000) 

AS 

BEGIN 



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 RETURN (NULL)

IF LEN(@prefix)=0 RETURN @String

IF LEN(@string)=0 RETURN ''



SELECT         @pos=8000,

       @WhereWeAre=1, 

       @UcasePrefix=STUFF(

            @prefix,1,1,

            UPPER(SUBSTRING(@prefix,1,1))),

       @PrefixLength=LEN(@prefix),

       @Vowels='aeiou'


WHILE @pos>0
    BEGIN
    SELECT @pos=PATINDEX('%['+@vowels+']%',
        SUBSTRING(@string,@whereWeAre,8000))
    IF @pos>0 
        BEGIN
        SELECT @firstVowel=SUBSTRING
             (@string,@whereWeAre+@pos-1,1)
        SELECT @String=STUFF(@string,@pos+@whereWeAre-1,1,
             CASE 
                 WHEN ASCII(@FirstVowel)<>ASCII(LOWER(@FirstVowel)) 
                 THEN @UcasePrefix 
                 ELSE @prefix 
             END 
             +LOWER(@FirstVowel))
        SELECT @next=@WhereWeAre+@pos+@PrefixLength-1
        SELECT @WhereWeAre=@next+PATINDEX('%[^'+@vowels+']%',
                               SUBSTRING(@string,@next,8000))
        END
    END
RETURN @String

END

/*

select dbo.ufsCeggode('This easily encodes English into gibberish','ob')

--Thobis obeasobily obencobodobes Obenglobish obintobo gobibboberobish 

select dbo.ufsCeggode('This easily encodes English into gibberish','egg')

--Theggis eggeaseggily eggenceggodegges Eggengleggish egginteggo
--geggibbeggereggish

select dbo.ufsCeggode('Everyone knows the euouae,

                        the closing words of the Doxology','egg') 

Eggeveggeryeggonegge kneggows thegge eggeuouae,

     thegge cleggosegging weggords eggof thegge Deggoxeggoleggogy



*/