REPLACE Multiple Spaces with One

Question

REPLACE Multiple Spaces with One

Viewing 15 posts - 121 through 135 (of 425 total)

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

jcrawf02 SSC-Insane Points: 24198 More actions · Answer 1

Thought I'd tell you, you guys are just fun to watch. 😉

---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]

"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."

Nadrek SSC-Insane Points: 20039 More actions · Answer 2

Jeff Moden (11/17/2009)
You and another poster said about the same thing... I'm going to have to give it a try. Although it's not likely to have 8000 spaces (almost silly to even think that), I'll have to expand your good code example to 13 replaces so they're functionally identical so far as VARCHAR(8000) goes and see how that performs, as well.

Not likely, perhaps... but very common to have about that many if someone started out with a CHAR(8000), even worse if they did concatenation on a CHAR(8000)+ another VARCHAR or CHAR + LEFT() or RIGHT() to get back to 8000. Some forms of data import also lead to excessive space padding. Are they good SQL? No. Do they happen? I believe they do.

Also, yes, GSquared and I do apparently use some similar methods.

GSquared SSC Guru Points: 260824 More actions · Answer 3

Nadrek (11/18/2009)
Jeff Moden (11/17/2009)
You and another poster said about the same thing... I'm going to have to give it a try. Although it's not likely to have 8000 spaces (almost silly to even think that), I'll have to expand your good code example to 13 replaces so they're functionally identical so far as VARCHAR(8000) goes and see how that performs, as well.
Not likely, perhaps... but very common to have about that many if someone started out with a CHAR(8000), even worse if they did concatenation on a CHAR(8000)+ another VARCHAR or CHAR + LEFT() or RIGHT() to get back to 8000. Some forms of data import also lead to excessive space padding. Are they good SQL? No. Do they happen? I believe they do.
Also, yes, GSquared and I do apparently use some similar methods.

I actually discovered this method almost 20 years ago, when I was building WordPerfect macros to clean up documents that had been typed up in an ASCII text editor. Used it to clean up spaces, underlines, even page-lines created by entering arbitrary numbers of hyphens. Came in very, very handy.

- 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

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

Ok folks,

1/everyone's SQL is now wrapped in functions

2/modifed SQL batch to capture results to table

3/added Michael Meierruth version with hard coded spaces

4/converted Michael's version to C# CLR

Here are the results:

(you will like this Paul - some variation on execution)

Beginning execution loop

Batch execution completed 10000 times.

SQL function: Replace Technique : but using replacement chars ||*9*9||

2776

SQL function :Brigzy (C#Screw) Looping

2596

SQL function: Jeff Original : single bell char

1996

CLR: Brigzy (C#Screw) Looping

453

SQL function: Michael Meierruth Original

333

SQL function: Michael Meierruth : with hard coded spaces

316

CLR: Not looping using ||*9*9|| technique in C#

290

CLR: using Jeff's single char technique in C#

250

CLR :using JCBnew space replacer C#

166

CLR: Michael Meierruth - C#Screw conversion: hard coded spaces

160

CLR: using Flo s space replacer C#

116

CLR: using Paul White space replacer C#

113

Note that Jeffs SQL has moved up now it is an SQL function

C# Gnu
____________________________________________________

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

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

For interest I improved test:

1/ ramped up workload from 10k rows to 50k rows,

2/ each function tested 10 times in succession, average the result:

Average exec times (50krows):

SQL function: Replace Technique : but using replacement chars ||*9*9||

13490

SQL function :Brigzy (C#Screw) Looping

12337

SQL function: Jeff Original : single bell char

9340

CLR: Brigzy (C#Screw) Looping

1641

SQL function: Michael Meierruth : with hard coded spaces

1562

SQL function: Michael Meierruth Original

1559

CLR: Not looping using ||*9*9|| technique in C#

1449

CLR: using Jeff's single char technique in C#

1187

CLR :using JCBnew space replacer C#

818

CLR: Michael Meierruth - C#Screw conversion: hard coded spaces

753

CLR: using Flo s space replacer C#

556

CLR: using Paul White space replacer C#

532

SQL F.Y.I.

--PREPARE

SET NOCOUNT ON

go

CREATE FUNCTION dbo.fn_CleanUp_Brigzy(@S VARCHAR(max))

RETURNS VARCHAR(max)

BEGIN

WHILE CHARINDEX(' ',@S) > 0

SELECT @s-2 = REPLACE(@S,' ',' ')

RETURN @s-2

END

go

CREATE FUNCTION dbo.fn_CleanUp_JeffOriginal(@S VARCHAR(max))

RETURNS VARCHAR(max)

BEGIN

RETURN REPLACE(

REPLACE(

LTRIM(RTRIM(@S)) ,' ',' '+CHAR(7)) --Changes 2 spaces to the OX model

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

,CHAR(7),'') --Changes the remaining X's to nothing

END

go

CREATE FUNCTION dbo.fn_CleanUp_MultiChars(@S VARCHAR(max))

RETURNS VARCHAR(max)

BEGIN

RETURN LTRIM(RTRIM(

REPLACE(REPLACE(REPLACE(@S,' ',' ||*9*9||'),'||*9*9|| ',''),'||*9*9||','')

))

END

GO

CREATE FUNCTION dbo.fn_CleanUp_MichaelMeierruth_MKII(@S VARCHAR(8000))

RETURNS VARCHAR(8000)

BEGIN

RETURN replace(replace(replace(replace(replace(replace(ltrim(rtrim(@s)),

' ',' '),

' ',' ')

END

go

CREATE FUNCTION dbo.fn_CleanUp_MichaelMeierruth_Original(@S VARCHAR(8000))

RETURNS VARCHAR(8000)

BEGIN

--declare @s-2 varchar(8000)

--set @s-2 = '*' + replicate(' ',7998) + '*'

--select len(@s)

set @s-2 = replace(replace(replace(replace(replace(replace(ltrim(rtrim(@s)),

replicate(' ',32+1),' '),replicate(' ',16+1),' '),replicate(' ',8+1),' '),replicate(' ',4+1),' '),replicate(' ',2+1),' '),replicate(' ',1+1),' ')

--select len(@s)

RETURN @s-2

END

go

CREATE TABLE #TEMP1 (COL1 VARCHAR(900))

CREATE TABLE #TEMP2 (COL2 VARCHAR(900), COL3 VARCHAR(900), COL4 VARCHAR(900),COL5 VARCHAR(900))

go

--INSERT 200k ROWS WITH RANDOM SPACES ON EACH TABLE, SEPARATE TABLES ARE USED TO AVOID CACHING, THIS MAY TAKE QUITE AWHILE

DECLARE @SPACECOUNT1 INT,@SPACECOUNT2 INT,@SPACECOUNT3 INT,@SPACECOUNT4 INT

SELECT @SPACECOUNT1 = CAST(CAST(NEWID() AS VARBINARY(1)) AS INT),@SPACECOUNT2 = CAST(CAST(NEWID() AS VARBINARY(1)) AS INT)

INSERT INTO #TEMP1 (COL1)

OUTPUT inserted.COL1 INTO #TEMP2 (COL2)

SELECT 'TEST1'+SPACE(@SPACECOUNT1)+'TEST2'+SPACE(@SPACECOUNT2)+'TEST3'

GO 50000

-- Test result table

CREATE TABLE #tResults(Tag VARCHAR(100),Result int)

go

-- Test 1 : Jeff technique : multi chars for replace char

DECLARE @StartTime DATETIME