• ChrisM@Work - Monday, December 11, 2017 9:57 AM

    Here's an alternative which is worth testing:

    DECLARE @String VARCHAR(2000) = 'the quick brown fox'

    SELECT

    REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(

    REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(

    ' ' + @String COLLATE LATIN1_GENERAL_BIN ,' z',' Z'),' y',' Y'),' x',' X'),' w',' W'),' v',' V'),' u',' U'),

    ' t',' T'),' s',' S'),' r',' R'),' q',' Q'),' p',' P'),' o',' O'),' n',' N'),' m',' M'),' l',' L'),' k',' K'),

    ' j',' J'),' i',' I'),' h',' H'),' g',' G'),' f',' F'),' e',' E'),' d',' D'),' c',' C'),' b',' B'),' a',' A')

    I have tested the above logic as Scalar & ITVF and it has good improvement. Below are the timings

    ================================================
    ========== Original Scalar Function ==========
    Duration = 00:01:20:077
    ================================================
    ========== Improved Scalar Function ==========
    Duration = 00:00:20:170
    ================================================
    ========== ITVF Function =====================
    Duration = 00:01:51:150
    ================================================
    ========== Chris Scalar Function =============
    Duration = 00:00:11:047
    ================================================
    ========== Chris ITVF Function ===============
    Duration = 00:00:08:300
    ================================================

    Thank you for the above link. Learnt something new today.