August 12, 2016 at 1:46 pm
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
August 12, 2016 at 1:59 pm
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.
August 12, 2016 at 2:41 pm
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