• I noticed that 'vliet' in a post earlier today seems to know about my binary approach calling it the "65, 33, 17, 9, 5, 3 an 2 spaces nested REPLACE construction".

    I did notice one bug in the code I published. It appears to not work for strings of length K*2^N+1 for K>1 and 2^N+1 corresponding to the largest string substitution you are doing (33). Strings of this length would end up in a string of length 2 (e.g. 65, 97, 129, etc). Thus one additional replace(@s," "," ") is needed to handle these relatively rare cases. Here is the correct version for C#Screw because this might slow things down a nanosecond or so and put me in a different place in the leaderboard.

    CREATE FUNCTION dbo.fn_CleanUp_MichaelMeierruth_MKII(@S VARCHAR(8000))



    RETURN replace(replace(replace(replace(replace(replace(replace(ltrim(rtrim(@s)),

    ' ',' '),

    ' ',' '),

    ' ',' '),

    ' ',' '),

    ' ',' '),

    ' ',' '),

    ' ',' ')


    I have also done some quick simulations to see maximum lengths of strings that can be handled with each value of N. These are the results so far:

    N=1 10

    N=2 38

    N=3 286

    N=4 4622

    N=5 151534

    Thus 2^N+1 = 33 for N=5 covers SQL Server's max length of 8000 quite nicely. No need for vliet's 65 for which I haven't figured out yet the maximum length string it could handle. From the above sequence it looks like something very big. Can anyone see the formula for this sequence?

    OK. Thought about it a bit.

    For N=6 it appears to be 9845678.

    The formula seems to be 2^(N+1)+Product for N=0 to N of 2^N+1

    E.g. for N=5 we get 64 + 33*17*9*5*3*2 = 151534