How can i make this sql server update statement better?

Question

I have this update statement below, is there a way of making it better ? i dont know if this will take a long time to compute in comparisons to the solutions out there.

update TableName 
set 
    ColumnTwoName = REPLACE(LTRIM(RTRIM(ColumnOneName)),'(',' '),
    ColumnTwoName = REPLACE(ColumnTwoName,')',' '),
    ColumnTwoName = REPLACE(ColumnTwoName,'+',' '),
    ColumnTwoName = REPLACE(ColumnTwoName,'*',' '),
    ColumnTwoName = REPLACE(ColumnTwoName,'&',' '),
    ColumnTwoName = REPLACE(ColumnTwoName,'@',' '),
    ColumnTwoName = REPLACE(ColumnTwoName,'|',' '),
    ColumnTwoName = REPLACE(ColumnTwoName,'/',' '),
    ColumnTwoName = REPLACE(ColumnTwoName,'!',' '),
    ColumnTwoName = REPLACE(ColumnTwoName,'.',' '),
    ColumnTwoName = REPLACE(ColumnTwoName,',',' '),
    ColumnTwoName = REPLACE(ColumnTwoName,':',' '),
    ColumnTwoName = REPLACE(ColumnTwoName,'£',' '),
    ColumnTwoName = REPLACE(ColumnTwoName,'?',' '),
    ColumnTwoName = REPLACE(ColumnTwoName,'''',''),
    ColumnTwoName = REPLACE(ColumnTwoName,'-',' '),
    ColumnTwoName = REPLACE(ColumnTwoName,']',' '),
    ColumnTwoName = REPLACE(ColumnTwoName,'%',' '),
    ColumnTwoName = REPLACE(ColumnTwoName,'  ',' '),
    ColumnTwoName = REPLACE(ColumnTwoName,'   ',' '),
    ColumnTwoName = REPLACE(LTRIM(RTRIM(ColumnTwoName)),' ','-'),
    ColumnTwoName = REPLACE(ColumnTwoName,'--','-'),
    ColumnTwoName = LOWER(ColumnTwoName)
where ColumnTwoName is null or ColumnTwoName = ''

This is invalid syntax and won't work. You can't update the same column more than once in a single update statement. You should instead nest your replace statements. — Sean Lange, May 28 '15 at 15:27

score 0 · Answer 1 · answered May 28 '15 at 15:36

0

There is not an easy way to format this many nested replaces so it looks nice. I left this pretty rough so you can so how this works.

LOWER(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
LTRIM(RTRIM(ColumnOneName))
,'(',' ') 
,')',' ')
,'+',' ')
,'*',' ')
,'&',' ')
,'@',' ')
,'|',' ')
,'/',' ')
,'!',' ')
,'.',' ')
,',',' ')
,':',' ')
,'£',' ')
,'?',' ')
,'''','')
,'-',' ')
,']',' ')
,'%',' ')
,'  ',' ')
,'   ',' ')
,' ','-')
,'--','-')
)

answered May 28 '15 at 15:36

Sean Lange

33,028
3
25
40

Hi, I am trying to understand this but it is pretty consfusing has it is not formatted, is it possible to format this ? – floormind May 28 '15 at 15:47
As I said, with this many levels it is really tough to format this nicely. It is just a LONG list of nested replaces. – Sean Lange May 28 '15 at 16:00
i got it something like this => update InstantWebPage set ColumnOneName= LOWER(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(LTRIM(RTRIM(ColumnOneName)),'(',' '),')',' '),'+',' '),'*',' '),'&',' '),'@',' '),'|',' '),'/',' '),'!',' '),'.',' '),',',' '),':',' '),'£',' '),'?',' '),'''',''),'-',' '),']',' '),'%',' '),' ',' '),' ',' '),' ','-'),'--','-')) where ColumnOneName is null or ColumnOneName = '' and ColumnThreeName = @ColumnThreeParam – floormind May 28 '15 at 16:04
Yeah...the reason I didn't cram all this into a single line is that it is impossible to maintain when it is all crammed together like that. – Sean Lange May 28 '15 at 16:39

score 0 · Answer 2 · edited May 23 '17 at 10:26

On doing the character replacements, here is a StackOverflow question that has some options for doing this more generally:

How to Replace Multiple Characters in SQL?

It seems the best idea is to create a CLR using regular expressions. A less efficient but functional method would be to create a scalar function. Doing all the nested replacements inline works fairly well too... but it doesn't look pretty and it's not very generic for reuse.

If you're trying to get rid of all duplicated spaces, the replacments you are doing won't work if there is a sufficently long number of spaces strung together. If you don't use a CLR, then you can use an update in a while loop to recursively get rid of extra spaces. Something like:

select @@ROWCOUNT
While @@ROWCOUNT > 0
update TableName
set ColumnTwoName = REPLACE(ColumnTwoName,'  ', ' ')
where ColumnTwoName like '%  %'

I just added the Select @@ROWCOUNT statement in this example to make sure that @@Rowcount is 1 so that it will enter the while loop the first time, but you can do whatever is needed to ensure that.

Check out this article. This is a set based approach using t-sql to replace multiple spaces with one. Much better than a loop. :D http://www.sqlservercentral.com/articles/T-SQL/68378/ — Sean Lange, May 28 '15 at 17:44
Very clever! Nice, thanks. The while loop method is less efficient, but it's not replacing them one at a time so at least it's removing them exponentially O(N^2). With the size of most strings it'll loop only a handful of times at the most. The other method linked to in this article would fit nicely in a scalar function to do all the replacements: http://www.sqlservercentral.com/Forums/Topic819042-203-15.aspx#bm821209 — Brian Pressler, May 28 '15 at 18:53

How can i make this sql server update statement better?

2 Answers2