Streaming itvf in CLR to replace special characters

  • Lynn, have you tried using your tally table and substring([input],N,1) to reduce the [input] string to a series of individual characters?

    You could put all your special codes to be replaced in a table, indexed on the codes themselves.

    Left join the output from line 1 to the table from line 2. When you get a hit on the join, substitute a space for the [input] character using a case statement.

    If you have big ranges of codes to replace, use a LIKE clauses in the case statement.

    Stitch it all back together with FOR XML.

    I have no clue about how the performance would work vs dozens of replace statements, but it HAS to be easier to manage.

    Sorry I'm at work right now and don't have time to code and test this, but I know you are savvy enough to put it together pretty quickly.

    P.S. Can you not reduce the 2 GB to smaller chunks and then put them all back together?

    __________________________________________________

    Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
    Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

  • The Dixie Flatline (8/12/2016)


    Lynn, have you tried using your tally table and substring([input],N,1) to reduce the [input] string to a series of individual characters?

    You could put all your special codes to be replaced in a table, indexed on the codes themselves.

    Left join the output from line 1 to the table from line 2. When you get a hit on the join, substitute a space for the [input] character using a case statement.

    If you have big ranges of codes to replace, use a LIKE clauses in the case statement.

    Stitch it all back together with FOR XML.

    I have no clue about how the performance would work vs dozens of replace statements, but it HAS to be easier to manage.

    Sorry I'm at work right now and don't have time to code and test this, but I know you are savvy enough to put it together pretty quickly.

    Many of the columns in our database are nvarchar data types and quite a few are nvarchar(max) and do have the potential of being quite long. I thought about using a tally table solution but with the requirement that some of the characters be deleted (n/char(0)) or replaced with a space or escaped that the case would be enormous. There may be a way to make it work, but I am not as involved with it now. More like a tech rep if there are questions. I would still like to find a better solution than the one currently in place which actually uses two functions to complete the task.

    Not to say that you haven't given me something to think about here, you have.

  • Hey Lynn,

    A quick example follows. It needs a lot of polish, but I just wanted to illustrate the concept.

    Like other SQL splitters, this shouldn't be used for strings over 8k.

    For the input sizes you're talking about a CLR is almost certainly going to run faster. Good luck 🙂

    create table #EvilCodes (evilcode char(1) not null primary key, rplwith char(1) not null)

    insert into #evilcodes

    values ('-','0')

    d eclare @input varchar(100) = convert(varchar(50),newid())+convert(varchar(50),newid());

    with cte as(select top(len(@input)) N, convert(char(1),substring(@input,N,1)) as InChar

    from vTally)

    ,cte2 as(select N, Inchar from cte where inchar not like '[A-Z]') -- letters are evil, eliminate them

    ,cte3 as(-- if a code is evil, replace it with rplwith

    select N, case when evilcode is null then inchar else rplwith end as outchar

    from cte2

    left join #EvilCodes on inchar = evilcode)

    select @input as [@input]

    , convert(varchar(100), (select ''+outchar

    from cte3

    order by N

    for xml path(''))) as FixedInput

    __________________________________________________

    Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
    Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

Viewing 3 posts - 16 through 18 (of 18 total)

You must be logged in to reply to this topic. Login to reply