Best Practice for character replacement within Table.Column

Question

Post reply

Best Practice for character replacement within Table.Column

YSLGuru

SSC-Insane

Points: 21661
More actions
November 22, 2023 at 8:16 pm

#4332665
Is there a better/more efficient way to do what the below does? It is replacing several characters that are known to be problematic for this table/column(s) with a space.
BONUS Q: IN the below example every character being replaced is being replaced with a space. What if I wanted to use a space for all but the & and = characters in which case I want to change the & to a PLUS and the = to and UNDERSCORE? Would the same solution work?
```
  UPDATE MM2WO 
  SET sDesc = Replace(Replace(Replace(Replace(Replace(Replace(sDesc,'&',' '),'<' , ' '),'>',' '),'!',' '), '-', ' '),'=',' ')
  WHERE 1 = 1 
    AND(    sDesc LIKE'%&%' 
         OR sDecs LIKE'%<%'
         OR sDesc LIKE'%>%' 
         OR sDesc LIKE'%!%' 
         OR sDesc LIKE'%-%' 
         OR sDesc LIKE'%=%'
     )
```
```
CREATE TABLE [dbo].[MM2WO](
[hMy] [NUMERIC](21, 0) IDENTITY(1,1) NOT NULL,
[sDesc] [CHAR](35) NULL,
 CONSTRAINT [pk_mm2wo] PRIMARY KEY NONCLUSTERED
```
- This topic was modified 1 year, 1 month ago by YSLGuru.
Kindest Regards,
Just say No to Facebook!

Viewing 15 posts - 1 through 14 (of 14 total)

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

ratbak Ten Centuries Points: 1382 More actions · Answer 1

You don't appear to have included the solution you wanted us to review.

YSLGuru SSC-Insane Points: 21661 More actions · Answer 2

That is what the first code segment is, the current solution. The second if for the tables structure, the DDL

This reply was modified 1 year, 1 month ago by YSLGuru.

Kindest Regards,

Just say No to Facebook!

Ed B Hall of Fame Points: 3669 More actions · Answer 3

I couldn't see the code until I quoted the post for reply, then my post was also hard to see. I pasted it in regular text as I couldnt make it visible.

  UPDATE MM2WO 

  SET sDesc = Replace(

Replace(Replace(Replace(Replace(Replace(sDesc,'&' ,' '), '<' , ' ') ,'>' ,' ') ,'!' ,' '), '-', ' '),'=',' ')

  WHERE 1 = 1 

    AND(    sDesc LIKE'%&%' 
         OR sDecs LIKE'%<%'
         OR sDesc LIKE'%>%' 
         OR sDesc LIKE'%!%' 
         OR sDesc LIKE'%-%' 
         OR sDesc LIKE'%=%'
     )

UPDATE MM2WO

SET sDesc = Replace(

Replace(Replace(Replace(Replace(Replace(sDesc,'&' ,' '), '<' , ' ') ,'>' ,' ') ,'!' ,' '), '-', ' '),'=',' ')

WHERE 1 = 1

AND( sDesc LIKE'%&%'

OR sDecs LIKE'%<%'

OR sDesc LIKE'%>%'

OR sDesc LIKE'%!%'

OR sDesc LIKE'%-%'

OR sDesc LIKE'%=%'

)

This reply was modified 1 year, 1 month ago by Ed B.
This reply was modified 1 year, 1 month ago by Ed B.

YSLGuru SSC-Insane Points: 21661 More actions · Answer 4

Gotcha. Hopefully someone like Jeff will have some clever way to improve on this as this is a regular fix I'm looking at having to do daily until our vendor can fix the issue that's inserting these problematic characters where they shouldn't be. I feel certain that some kind of temp table maybe combined with a Windows function can simplify this.

Thanks

Kindest Regards,

Just say No to Facebook!

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 5

YSLGuru wrote:

Is there a better/more efficient way to do what the below does? It is replacing several characters that are known to be problematic for this table/column(s) with a space.
BONUS Q: IN the below example every character being replaced is being replaced with a space. What if I wanted to use a space for all but the & and = characters in which case I want to change the & to a PLUS and the = to and UNDERSCORE? Would the same solution work?
 
 UPDATE MM2WO 
  SET sDesc = Replace(Replace(Replace(Replace(Replace(Replace(sDesc,'&',' '),'<' , ' '),'>',' '),'!',' '), '-', ' '),'=',' ')
  WHERE 1 = 1 
    AND(    sDesc LIKE'%&%' 
         OR sDecs LIKE'%<%'
         OR sDesc LIKE'%>%' 
         OR sDesc LIKE'%!%' 
         OR sDesc LIKE'%-%' 
         OR sDesc LIKE'%=%'
     )
CREATE TABLE [dbo].[MM2WO](
[hMy] [NUMERIC](21, 0) IDENTITY(1,1) NOT NULL,
[sDesc] [CHAR](35) NULL,
 CONSTRAINT [pk_mm2wo] PRIMARY KEY NONCLUSTERED

I quoted your entire original post to see if the "goodies" that didn't appear in the first post would show up. They did in my edit window and now I'm looking to see if the show up in the post that quotes them. I really wish they'd fix the editor code and a bunch of other things that have been broken ever since the switch to Word Press years ago. 🙁

EDIT... fascinating mess being made by the forum software on the first bit of code. Apparently, it has some combination of characters in the code that's blowing the mind of the code window.

Lets see if just plain text posted works...

UPDATE MM2WO

SET sDesc = Replace(Replace(Replace(Replace(Replace(Replace(sDesc,'&',' '),'<' , ' '),'>',' '),'!',' '), '-', ' '),'=',' ')

WHERE 1 = 1

AND( sDesc LIKE'%&%'

OR sDecs LIKE'%<%'

OR sDesc LIKE'%>%'

OR sDesc LIKE'%!%'

OR sDesc LIKE'%-%'

OR sDesc LIKE'%=%'

)

OK 😀 That worked. Marching on.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 6

All-righty then...

You say that you want all of the special characters to be replaced by spaces except for "&" to be replaced by "+" and "=" to be replaced by a "_".

My question would be, what's stopping you from doing that in your REPLACEs? The answer is ... Nothing.

UPDATE MM2WO

SET sDesc = Replace(Replace(Replace(Replace(Replace(Replace(sDesc,'&','+'),'<' , ' '),'>',' '),'!',' '), '-', ' '),'=','_')

WHERE 1 = 1

AND( sDesc LIKE'%&%'

OR sDecs LIKE'%<%'

OR sDesc LIKE'%>%'

OR sDesc LIKE'%!%'

OR sDesc LIKE'%-%'

OR sDesc LIKE'%=%'

);

You might be able to short-circuit the WHERE clause a bit.

UPDATE MM2WO

SET sDesc = Replace(Replace(Replace(Replace(Replace(Replace(sDesc,'&','+'),'<' , ' '),'>',' '),'!',' '), '-', ' '),'=','_')

WHERE 1 = 1

AND sDesc LIKE'%[-&<>!=]%' ;

Depending on your default collation, you might be able to seriously increase your speed by adding a binary collation to the occurrences of the sDesc in the formula and the AND.

UPDATE MM2WO

SET sDesc = Replace(Replace(Replace(Replace(Replace(Replace(sDesc COLLATE Latin1_General_BIN2,'&','+'),'<' , ' '),'>',' '),'!',' '), '-', ' '),'=','_')

WHERE 1 = 1

AND sDesc COLLATE Latin1_General_BIN2 LIKE'%[-&<>!=]%' ;

Just to demonstrate that it works...

DECLARE @sDesc VARCHAR(100) = 'X&X<X>X!X-X=';
SELECT Replace(Replace(Replace(Replace(Replace(Replace(@sDesc COLLATE Latin1_General_BIN2,'&','+'),'<' , ' '),'>',' '),'!',' '), '-', ' '),'=','_')
WHERE 1 = 1
AND  @sDesc COLLATE Latin1_General_BIN2 LIKE'%[-&<>!=]%';

Result:

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

YSLGuru SSC-Insane Points: 21661 More actions · Answer 7

Jeff - that's what I am doing now, just using Replace(). I wanted to know if there was a better way to do it and so far it looks like there's not a better way so much as another way. This particular update could take awhile to run due to the number of possible records it may find so I was looking for any alternatives that would provide a significant boost in performance but it's not important that we find another way.

Thanks to all

Kindest Regards,

Just say No to Facebook!

DesNorton SSC-Insane Points: 24416 More actions · Answer 8

Starting with SQL 2017, you can use the TRANSLATE function

How to post data/code on a forum to get the best help.

Phil Parkin SSC Guru Points: 246988 More actions · Answer 9

YSLGuru wrote:

Jeff - that's what I am doing now, just using Replace(). I wanted to know if there was a better way to do it and so far it looks like there's not a better way so much as another way. This particular update could take awhile to run due to the number of possible records it may find so I was looking for any alternatives that would provide a significant boost in performance but it's not important that we find another way.
Thanks to all

Jeff also suggested a refinement to the used of REPLACE() which might speed things up. Did you try it?

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

YSLGuru SSC-Insane Points: 21661 More actions · Answer 10

Unfortunately our cloud provider still has us on SQL 2016.

Thanks

Kindest Regards,

Just say No to Facebook!

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 11

YSLGuru wrote:

Jeff - that's what I am doing now, just using Replace(). I wanted to know if there was a better way to do it and so far it looks like there's not a better way so much as another way. This particular update could take awhile to run due to the number of possible records it may find so I was looking for any alternatives that would provide a significant boost in performance but it's not important that we find another way.
Thanks to all

Heh... "Always look eye". 😛 Using nested REPLACEs with a BINARY COLLATION is going to be really hard to beat.

Here's the test code...

--===== Create and populate the test table on-the-fly
   DROP TABLE IF EXISTS #MyHead;
 SELECT TOP 1000000
        Original = CONVERT(VARCHAR(36),NEWID())
   INTO #MyHead
   FROM sys.all_columns ac1 
  CROSS JOIN sys.all_columns ac2
;
GO
  PRINT '--===== Prime the pumps" for the tests ===========';
    SET STATISTICS TIME ON;
DECLARE @Result VARCHAR(36);
 SELECT @Result = Original
   FROM #MyHead;
    SET STATISTICS TIME OFF;
GO
  PRINT '--===== Use TRANSLATE ============================';
DECLARE @Result VARCHAR(36);
    SET STATISTICS TIME ON;
 SELECT @Result = TRANSLATE(Original,'AE-','  &')
   FROM #MyHead;
    SET STATISTICS TIME OFF;
GO
  PRINT '--===== Use REPLACE w/Binary Collation ===========';
DECLARE @Result VARCHAR(36);
    SET STATISTICS TIME ON;
 SELECT @Result = REPLACE(REPLACE(REPLACE(Original COLLATE Latin1_General_BIN2,'A',' '),'E',' '),'-','&')
   FROM #MyHead;
    SET STATISTICS TIME OFF;
GO

Here are the results...

(1000000 rows affected)
--===== Prime the pumps" for the tests ===========

 SQL Server Execution Times:
   CPU time = 157 ms,  elapsed time = 143 ms.
--===== Use TRANSLATE ============================

 SQL Server Execution Times:
   CPU time = 2343 ms,  elapsed time = 2336 ms.
--===== Use REPLACE w/Binary Collation ===========

 SQL Server Execution Times:
   CPU time = 1125 ms,  elapsed time = 1160 ms.

IMPORTANT OTHER CONSIDERATIONS FOR THIS PROBLEM:

First, remember that binary collations ARE CASE/ACCENT SENSITIVE.

If you're using nested REPLACEs with the Binary Collation and the code still runs slow, it's NOT because of the REPLACEs... there's something wrong with the rest of your code.

Personally, I wouldn't do an UPDATE, which is going to beat the hell out of your logfile and make things slower, especially if you have indexes that will be affected. Instead, I'd turn this into a PERSISTED Computed column.

Either that or do the REPLACEs on the data as you add the rows to the table in the future.

Sidebar:

As a bit of a sidebar, this is more proof that "Change is inevitable, change for the better is not". MS probably used some form of "Regex Replace" behind the scenes for the new TRANSLATE function and that makes it slow. It's likely the same issue they have with the FORMAT function.

https://www.sqlservercentral.com/articles/how-to-format-dates-in-sql-server-hint-dont-use-format

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jonathan AC Roberts SSCoach Points: 18363 More actions · Answer 12

UPDATE MM2WO
SET sDesc = TRANSLATE(sDesc, '&<!>!-=', '      ')
WHERE ...

https://learn.microsoft.com/en-us/sql/t-sql/functions/translate-transact-sql?view=sql-server-ver16

Phil Parkin SSC Guru Points: 246988 More actions · Answer 13

Jonathan AC Roberts wrote:

UPDATE MM2WO
SET sDesc = TRANSLATE(sDesc, '&<!>!-=', '      ')
WHERE ...
https://learn.microsoft.com/en-us/sql/t-sql/functions/translate-transact-sql?view=sql-server-ver16

TRANSLATE not available in 2016.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Jonathan AC Roberts SSCoach Points: 18363 More actions · Answer 14

Phil Parkin wrote:

Jonathan AC Roberts wrote:
UPDATE MM2WO
SET sDesc = TRANSLATE(sDesc, '&<!>!-=', '      ')
WHERE ...
https://learn.microsoft.com/en-us/sql/t-sql/functions/translate-transact-sql?view=sql-server-ver16
TRANSLATE not available in 2016.

Oh yes, I was looking at the "ver16" in the help URL.