REPLACE Multiple Spaces with One

Question

REPLACE Multiple Spaces with One

Viewing 15 posts - 76 through 90 (of 425 total)

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

Rob Fisk SSCommitted Points: 1861 More actions · Answer 1

Paul White (11/17/2009)
SDM (11/17/2009)
To the people who can program in a .NET language and recognise when to use it appropriately to complement T-SQL:
Fixed that for ya! 😀
SDM (11/17/2009)
The OP stated it had to be SQL 2000.
OP? It's an article! And where does it specify 2000? Read it again carefully 😛

Just under the first code segment:

The goal is to convert the sections of multiple spaces of unknown length to a single space each as quickly as possible. There are some additional requirements. You can't make the data any bigger during the process because it might get too big for VARCHAR(8000) (or whatever size the column is) or you can't use VARCHAR(MAX) because you're using SQL Server 2000

If you are going to ask someone to read the article carfully then I suggest doing so yourself 😀

[font="Arial"]_______________________________________________________
Change is inevitable... Except from a vending machine.[/font]

Florian Reischl SSC-Dedicated Points: 37301 More actions · Answer 2

@brigzy

You really should try your tests with a complete table to be handled instead of single strings. "GO N" is a feature of SSMS which causes "N" separate statements to be send over the network.

Try this:

PRINT CONVERT(VARCHAR(30), GETDATE(), 126);

GO

DECLARE @i INT;

SELECT @i = 1;

GO 10000

PRINT CONVERT(VARCHAR(30), GETDATE(), 126);

GO

I get this result:

2009-11-17T10:22:36.710

Beginning execution loop

Batch execution completed 10000 times.

2009-11-17T10:22:39.150

As you see, I have almost 2,5 seconds for doing nothing 😉

Greets

Flo

Paul White SSC Guru Points: 150467 More actions · Answer 3

Rob Fisk (11/17/2009)
Just under the first code segment:
The goal is to convert the sections of multiple spaces of unknown length to a single space each as quickly as possible. There are some additional requirements. You can't make the data any bigger during the process because it might get too big for VARCHAR(8000) (or whatever size the column is) or you can't use VARCHAR(MAX) because you're using SQL Server 2000
If you are going to ask someone to read the article carfully then I suggest doing so yourself 😀

Easy Rob!!! I did read that bit (it's the only place that matches a search for '2000'. But you tell me how the grammar works there. That's an OR. The way I read it, I'm not using 2000 so it doesn't apply 😛 :w00t: 😛

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Rob Fisk SSCommitted Points: 1861 More actions · Answer 4

My mind converted the OR to an AND since if you could use VARCHAR(MAX) then the first constraint about string length would not apply anyway.

[font="Arial"]_______________________________________________________
Change is inevitable... Except from a vending machine.[/font]

Tom Garth SSCertifiable Points: 6193 More actions · Answer 5

bornsql (11/16/2009)
Hi how about this code whether this will work fine or not
DECLARE @tmpstr2 Varchar(MAX)
SELECT @tmpstr2=replace(OriginalString, char(10) + char(13), '') FROM @demo
SELECT @tmpstr2=replace(@tmpstr2,' ','|')
SELECT @tmpstr2=replace(@tmpstr2,'|','')
SELECT @tmpstr2

In the end this..

The goal is to remove extra spaces.
Always leaving 1 space.
It removes all of the spaces.
It also removes all LF-CR combinationations.
This only works when they are in that order.
Much if not most of the time they would be CR-LF.
Doing them one at a time would work better for generic coding

Looks Like this...

Thegoalistoremoveextraspaces.Alwaysleaving1space.Itremovesallofthespaces.ItalsoremovesallLF-CRcombinationations.Thisonlyworkswhentheyareinthatorder.MuchifnotmostofthetimetheywouldbeCR-LF.Doingthemoneatatimewouldworkbetterforgenericcoding.

Tom Garth
Vertical Solutions[/url]

"There are three kinds of men. The one that learns by reading. The few who learn by observation. The rest of them have to pee on the electric fence for themselves." -- Will Rogers

GSquared SSC Guru Points: 260824 More actions · Answer 6

TheSQLGuru (11/16/2009)
GSquared: did you try hard-coded space-strings instead of the pile of replicates? I would HOPE the optimizer would expand those on compile, but if it doesn't that should shave off some CPU ticks....

Yes. It does nothing for the speed, and makes the code a lot harder to read.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 7

GSquared (11/17/2009)
TheSQLGuru (11/16/2009)
GSquared: did you try hard-coded space-strings instead of the pile of replicates? I would HOPE the optimizer would expand those on compile, but if it doesn't that should shave off some CPU ticks....
Yes. It does nothing for the speed, and makes the code a lot harder to read.

Thanks for the followup G2! I had figured the compiler would expand those in the query plan, but dumber things have happened!! 🙂

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Pieter-423357 SSCommitted Points: 1619 More actions · Answer 8

Pretty good trick, but do you really want to subject each row to the triple REPLACE function?

REPLACE is a costly function and will run for a good while when you process millions of rows. I would add something to screen out entries with 2 or more spaces and perform action on those alone.

Rob Fisk SSCommitted Points: 1861 More actions · Answer 9

Pieter-423357 (11/17/2009)
Pretty good trick, but do you really want to subject each row to the triple REPLACE function?
REPLACE is a costly function and will run for a good while when you process millions of rows. I would add something to screen out entries with 2 or more spaces and perform action on those alone.

The code sample in the article has the clause

WHERE CHARINDEX(' ',OriginalString) > 0

[font="Arial"]_______________________________________________________
Change is inevitable... Except from a vending machine.[/font]

C# Gnu SSCertifiable Points: 5973 More actions · Answer 10

Florian Reischl (11/17/2009)
@brigzy
You really should try your tests with a complete table to be handled instead of single strings. "GO N" is a feature of SSMS which causes "N" separate statements to be send over the network.
Try this:
PRINT CONVERT(VARCHAR(30), GETDATE(), 126);
GO
DECLARE @i INT;
SELECT @i = 1;
GO 10000
PRINT CONVERT(VARCHAR(30), GETDATE(), 126);
GO
I get this result:
2009-11-17T10:22:36.710
Beginning execution loop
Batch execution completed 10000 times.
2009-11-17T10:22:39.150
As you see, I have almost 2,5 seconds for doing nothing 😉
Greets
Flo

You really should address Thiago who wrote the test data generation SQL. Thiago's script does generate a complete table quite nicely, this we used for testing the techniques. It takes 2 secs to generate the test data table, and not to 'do nothing'.

C# Gnu
____________________________________________________

Excel Database Tasks : efficient business data processing
Version 3.3 released February 2015

Paul White SSC Guru Points: 150467 More actions · Answer 11

brigzy (11/17/2009)
You really should address Thiago who wrote the test data generation SQL. Thiago's script does generate a complete table quite nicely, this we used for testing the techniques. It takes 2 secs to generate the test data table, and not to 'do nothing'.

True, but to be fair I think Flo was just trying to be helpful - he writes a pretty mean test script 🙂

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Florian Reischl SSC-Dedicated Points: 37301 More actions · Answer 12

Hi

brigzy (11/17/2009)
You really should address Thiago who wrote the test data generation SQL. Thiago's script does generate a complete table quite nicely, this we used for testing the techniques. It takes 2 secs to generate the test data table, and not to 'do nothing'.

:w00t:

Oups. Apparently I misread his script. Sorry for that.

Just never saw "GO N" to create test data 😉

Greets

Flo

C# Gnu SSCertifiable Points: 5973 More actions · Answer 13

Paul White (11/17/2009)
brigzy (11/17/2009)
You really should address Thiago who wrote the test data generation SQL. Thiago's script does generate a complete table quite nicely, this we used for testing the techniques. It takes 2 secs to generate the test data table, and not to 'do nothing'.
True, but to be fair I think Flo was just trying to be helpful - he writes a pretty mean test script 🙂

Well

try your tests with a complete table to be handled instead of single strings

and

2,5 seconds for doing nothing

not fair on Theago.

C# Gnu
____________________________________________________

Excel Database Tasks : efficient business data processing
Version 3.3 released February 2015

Paul White SSC Guru Points: 150467 More actions · Answer 14

brigzy (11/17/2009)
Well
try your tests with a complete table to be handled instead of single strings

Good advice.

brigzy (11/17/2009)
and
2,5 seconds for doing nothing
not fair on Theago.

I think you misread the intention - to illustrate the cost of the network round trips. But never mind.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

C# Gnu SSCertifiable Points: 5973 More actions · Answer 15

Good advice.

? if u check out script ... it does gen table ...

C# Gnu
____________________________________________________

Excel Database Tasks : efficient business data processing
Version 3.3 released February 2015