Can't remove multiple char(32) strings

Question

Post reply

Can't remove multiple char(32) strings

david_h_edmonds

Default port

Points: 1430
More actions
October 26, 2018 at 6:01 am

#380345

Hi, I have some double spaces in text strings that I would like to remove.
I have tried the usual script and they remain no matter what I do.
Here is a script I have been told to try yet this won't remove them either.
Script and sample data attached.
Please can you offer any insight into this?
Sample Data:
address
UNION STREET RYDE ISLE OF WIGHT
UNION STREET RYDE ISLE OF WIGHT
update dbo.Clean_Hotelbookings
set address =
(select
distinct
REPLACE(
REPLACE(
REPLACE(
LTRIM(RTRIM(address))
,' ',' '+CHAR(7)) --Changes 2 spaces to the OX model
,CHAR(7)+' ','') --Changes the XO model to nothing
,CHAR(7),'') AS address --Changes the remaining X's to nothing
from dbo.Clean_Hotelbookings hb where hb.bookingRef = bookingRef)
Thanks
Dave
P.S. the spaces are all char(32)

Viewing 8 posts - 1 through 7 (of 7 total)

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

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

Unusually complicated for a simple update:

UPDATE dbo.clean_hotelbookings

SET address = REPLACE(

REPLACE(

LTRIM(RTRIM(address))

,' ',' '+CHAR(7)) --Changes 2 spaces to the OX model

,CHAR(7)+' ','') --Changes the XO model to nothing

,CHAR(7),'')

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

LinksUp SSCertifiable Points: 6526 More actions · Answer 2

david_h_edmonds - Friday, October 26, 2018 6:01 AM
Sample Data:
address
UNION STREET RYDE ISLE OF WIGHT
UNION STREET RYDE ISLE OF WIGHT
Thanks
Dave
P.S. the spaces are all char(32)

update hb set address = REPLACE( REPLACE( REPLACE( RTRIM(LTRIM(address)) ,' ',' '+CHAR(7)) --Changes 2 spaces to the OX model ,CHAR(7)+' ','') --Changes the XO model to nothing ,CHAR(7),'') AS address --Changes the remaining X's to nothing from dbo.Clean_Hotelbookings hb where hb.bookingRef = bookingRef

If would be helpful if you stated exactly what did not work. I have tested the original code that you are using (Thanks Jeff Moden!!) and it works perfectly.
Also if you supplied DDL and consumable data that represents your data, a more specific reason as to why it is not working could be provided.

__________________________________________________________________________________________________________
How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

A simple test harness shows that the method works:

;WITH Clean_Hotelbookings AS (

SELECT * FROM (VALUES

('UNION STREET RYDE ISLE OF WIGHT'),

('UNION STREET RYDE ISLE OF WIGHT')

) d ([address])

)

SELECT

[address],

Newaddress = REPLACE(

REPLACE(

LTRIM(RTRIM(address))

,' ',' '+CHAR(7)) --Changes 2 spaces to the OX model

,CHAR(7)+' ','') --Changes the XO model to nothing

,CHAR(7),'')

FROM Clean_Hotelbookings

If you have "spaces" remaining after the code has run, they might not be spaces...

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Jeff Moden SSC Guru Points: 1004412 More actions · Answer 4

ChrisM@Work - Friday, October 26, 2018 6:59 AM
Unusually complicated for a simple update:
UPDATE dbo.clean_hotelbookings
SET address = REPLACE(
REPLACE(
REPLACE(
LTRIM(RTRIM(address))
,' ',' '+CHAR(7)) --Changes 2 spaces to the OX model
,CHAR(7)+' ','') --Changes the XO model to nothing
,CHAR(7),'')

Ah... I know where you may have found such a thing (possibly here http://www.sqlservercentral.com/articles/T-SQL/68378/) and I have to tell you that it's actually pretty slow (I DID make the same mistake). In the discussion that followed the article, a much better way (and a whole lot faster) was demonstrated and tested to death. You've gotta love this community... it was a great discussion with an awesome outcome... much better than what I wrote in the article. With that in mind, I added (back in 2012) a "Prologue" to the article that has two links to much better solutions that appeared in the discussion. I recommend using one of those two other solutions depending on whether collation is important or not.

--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)

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

Jeff Moden - Friday, October 26, 2018 9:05 AM
ChrisM@Work - Friday, October 26, 2018 6:59 AM
Unusually complicated for a simple update:
UPDATE dbo.clean_hotelbookings
SET address = REPLACE(
REPLACE(
REPLACE(
LTRIM(RTRIM(address))
,' ',' '+CHAR(7)) --Changes 2 spaces to the OX model
,CHAR(7)+' ','') --Changes the XO model to nothing
,CHAR(7),'')
Ah... I know where you may have found such a thing (possibly here http://www.sqlservercentral.com/articles/T-SQL/68378/) and I have to tell you that it's actually pretty slow (I DID make the same mistake). In the discussion that followed the article, a much better way (and a whole lot faster) was demonstrated and tested to death. You've gotta love this community... it was a great discussion with an awesome outcome... much better than what I wrote in the article. With that in mind, I added (back in 2012) a "Prologue" to the article that has two links to much better solutions that appeared in the discussion. I recommend using one of those two other solutions depending on whether collation is important or not.

Ah yes, I remember following a couple of those at the time. IIRC setting the collation in the REPLACE yielded a decent performance lift.

In this case I was thinking more in terms of the syntax of the UPDATE. It's not necessary for it to be a joined update:

UPDATE dbo.Clean_Hotelbookings

SET [address] = (

SELECT some_stuff

FROM dbo.Clean_Hotelbookings hb

WHERE hb.bookingRef = bookingRef

)

Note the join on bookingRef. The first reference to it is qualified with the alias of the inner table. The second reference isn't qualified, and although it's technically ambiguous because it could be either an inner or outer reference, no error is thrown and SQL Server assumes that it's an inner reference. Here's what that predicate looks like, grabbed from the plan:

[database].[dbo].[Clean_Hotelbookings].[bookingRef] as [hb].[bookingRef] = [database].[dbo].[Clean_Hotelbookings ].[bookingRef] as [hb].[bookingRef]

Notice the unqualified reference is now qualified with the alias of the inner table.
Constructing the update statement in the simplest way will prevent this catastrophe from occurring.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

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

ChrisM@Work - Monday, October 29, 2018 3:19 AM
Jeff Moden - Friday, October 26, 2018 9:05 AM
ChrisM@Work - Friday, October 26, 2018 6:59 AM
Unusually complicated for a simple update:
UPDATE dbo.clean_hotelbookings
SET address = REPLACE(
REPLACE(
REPLACE(
LTRIM(RTRIM(address))
,' ',' '+CHAR(7)) --Changes 2 spaces to the OX model
,CHAR(7)+' ','') --Changes the XO model to nothing
,CHAR(7),'')
Ah... I know where you may have found such a thing (possibly here http://www.sqlservercentral.com/articles/T-SQL/68378/) and I have to tell you that it's actually pretty slow (I DID make the same mistake). In the discussion that followed the article, a much better way (and a whole lot faster) was demonstrated and tested to death. You've gotta love this community... it was a great discussion with an awesome outcome... much better than what I wrote in the article. With that in mind, I added (back in 2012) a "Prologue" to the article that has two links to much better solutions that appeared in the discussion. I recommend using one of those two other solutions depending on whether collation is important or not.
Ah yes, I remember following a couple of those at the time. IIRC setting the collation in the REPLACE yielded a decent performance lift.
In this case I was thinking more in terms of the syntax of the UPDATE. It's not necessary for it to be a joined update:
UPDATE dbo.Clean_Hotelbookings
SET [address] = (
SELECT some_stuff
FROM dbo.Clean_Hotelbookings hb
WHERE hb.bookingRef = bookingRef
)
Note the join on bookingRef. The first reference to it is qualified with the alias of the inner table. The second reference isn't qualified, and although it's technically ambiguous because it could be either an inner or outer reference, no error is thrown and SQL Server assumes that it's an inner reference. Here's what that predicate looks like, grabbed from the plan:
[database].[dbo].[Clean_Hotelbookings].[bookingRef] as [hb].[bookingRef] = [database].[dbo].[Clean_Hotelbookings ].[bookingRef] as [hb].[bookingRef]
Notice the unqualified reference is now qualified with the alias of the inner table.
Constructing the update statement in the simplest way will prevent this catastrophe from occurring.

I think that maybe you were thinking of a different post?

--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)

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 7

Jeff Moden - Monday, October 29, 2018 6:42 AM
ChrisM@Work - Monday, October 29, 2018 3:19 AM
Jeff Moden - Friday, October 26, 2018 9:05 AM
ChrisM@Work - Friday, October 26, 2018 6:59 AM
Unusually complicated for a simple update:
UPDATE dbo.clean_hotelbookings
SET address = REPLACE(
REPLACE(
REPLACE(
LTRIM(RTRIM(address))
,' ',' '+CHAR(7)) --Changes 2 spaces to the OX model
,CHAR(7)+' ','') --Changes the XO model to nothing
,CHAR(7),'')
Ah... I know where you may have found such a thing (possibly here http://www.sqlservercentral.com/articles/T-SQL/68378/) and I have to tell you that it's actually pretty slow (I DID make the same mistake). In the discussion that followed the article, a much better way (and a whole lot faster) was demonstrated and tested to death. You've gotta love this community... it was a great discussion with an awesome outcome... much better than what I wrote in the article. With that in mind, I added (back in 2012) a "Prologue" to the article that has two links to much better solutions that appeared in the discussion. I recommend using one of those two other solutions depending on whether collation is important or not.
Ah yes, I remember following a couple of those at the time. IIRC setting the collation in the REPLACE yielded a decent performance lift.
In this case I was thinking more in terms of the syntax of the UPDATE. It's not necessary for it to be a joined update:
UPDATE dbo.Clean_Hotelbookings
SET [address] = (
SELECT some_stuff
FROM dbo.Clean_Hotelbookings hb
WHERE hb.bookingRef = bookingRef
)
Note the join on bookingRef. The first reference to it is qualified with the alias of the inner table. The second reference isn't qualified, and although it's technically ambiguous because it could be either an inner or outer reference, no error is thrown and SQL Server assumes that it's an inner reference. Here's what that predicate looks like, grabbed from the plan:
[database].[dbo].[Clean_Hotelbookings].[bookingRef] as [hb].[bookingRef] = [database].[dbo].[Clean_Hotelbookings ].[bookingRef] as [hb].[bookingRef]
Notice the unqualified reference is now qualified with the alias of the inner table.
Constructing the update statement in the simplest way will prevent this catastrophe from occurring.
I think that maybe you were thinking of a different post?

Oops...yes I was, thanks Jeff.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden