Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««1314151617»»»

REPLACE Multiple Spaces with One Expand / Collapse
Author
Message
Posted Wednesday, November 18, 2009 1:48 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Sunday, November 4, 2012 12:23 PM
Points: 2,087, Visits: 3,932
Hi C# Screw

Good point, maybe try this instead of the StringBuilder:
string[] parts = s.Split(new char[] { ' ', '\t' });
return string.Join(" ", parts);

But I think the char-loop based solutions will still be much faster, since they do less memory copies.

Greets
Flo



The more I learn, the more I know what I do not know
Blog: Things about Software Architecture, .NET development and T-SQL

How to Post Data/Code to get the best Help How to Post Performance Problems
Post #821168
Posted Wednesday, November 18, 2009 1:54 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:36 AM
Points: 11,192, Visits: 11,091
Flo - thank you so much for the Tui billboard

I should have known you'd get your revenge! Man, that made me snigger out loud at my desk....!




Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #821172
Posted Wednesday, November 18, 2009 1:59 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Sunday, November 4, 2012 12:23 PM
Points: 2,087, Visits: 3,932
Paul White (11/18/2009)
Flo - thank you so much for the Tui billboard

I should have known you'd get your revenge! Man, that made me snigger out loud at my desk....!

Always welcome!

Remember, what you give is what you get.
(... Damn! Yesterday I just called you crazy )



The more I learn, the more I know what I do not know
Blog: Things about Software Architecture, .NET development and T-SQL

How to Post Data/Code to get the best Help How to Post Performance Problems
Post #821177
Posted Wednesday, November 18, 2009 2:02 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 4:13 AM
Points: 225, Visits: 643
Florian Reischl (11/18/2009)
Hi C# Screw

Good point, maybe try this instead of the StringBuilder:
string[] parts = s.Split(new char[] { ' ', '\t' });
return string.Join(" ", parts);

But I think the char-loop based solutions will still be much faster, since they do less memory copies.

Greets
Flo

Hi Flo
I tried the above but it is returning the original string


C# Gnu
____________________________________________________


Multi user data edit / data transfer Excel Task based solution
Version 2.7 released June 2013
Post #821179
Posted Wednesday, November 18, 2009 2:07 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Sunday, November 4, 2012 12:23 PM
Points: 2,087, Visits: 3,932
C# Screw (11/18/2009)
Florian Reischl (11/18/2009)
Hi C# Screw

Good point, maybe try this instead of the StringBuilder:
string[] parts = s.Split(new char[] { ' ', '\t' });
return string.Join(" ", parts);

But I think the char-loop based solutions will still be much faster, since they do less memory copies.

Greets
Flo

Hi Flo
I tried the above but it is returning the original string

Oups, forgot the StringSplitOptions... Try this:
string[] parts = s.Split(new char[] { ' ', '\t' }, StringSplitOptions.RemoveEmptyEntries);
return string.Join(" ", parts);

Greets
Flo

Edit:Removed the leading spaces



The more I learn, the more I know what I do not know
Blog: Things about Software Architecture, .NET development and T-SQL

How to Post Data/Code to get the best Help How to Post Performance Problems
Post #821184
Posted Wednesday, November 18, 2009 2:14 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 4:13 AM
Points: 225, Visits: 643
Yep thats it Flo:

10k rows
10x execution AVG

SQL function: Replace Technique : but using replacement chars ||*9*9||
2648
SQL function :Brigzy (C#Screw) Looping
2538
SQL function: Jeff Original : single bell char
1891
CLR: Brigzy (C#Screw) Looping
348
SQL function: Michael Meierruth : with hard coded spaces
342
SQL function: Michael Meierruth Original
317
CLR: Not looping using ||*9*9|| technique in C#
296
CLR: using Jeff's single char technique in C#
255
CLR: C#Screw II - Spaces and TABS
197
CLR: Flo - Spaces and TABS - v.compact
169

CLR :using JCBnew space replacer C#
162
CLR: Michael Meierruth - C#Screw conversion: hard coded spaces
152
CLR: using Paul White space replacer C#
109
CLR: using Flo s space replacer C#
108


C# Gnu
____________________________________________________


Multi user data edit / data transfer Excel Task based solution
Version 2.7 released June 2013
Post #821191
Posted Wednesday, November 18, 2009 2:21 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 4:13 AM
Points: 225, Visits: 643
Oh look ! I have become a ValuedMember

Screw


C# Gnu
____________________________________________________


Multi user data edit / data transfer Excel Task based solution
Version 2.7 released June 2013
Post #821197
Posted Wednesday, November 18, 2009 2:23 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Sunday, November 4, 2012 12:23 PM
Points: 2,087, Visits: 3,932
C# Screw (11/18/2009)
Oh look ! I have become a ValuedMember

Congrats Screw!
And thanks for all your time to do those tests

Greets
Flo



The more I learn, the more I know what I do not know
Blog: Things about Software Architecture, .NET development and T-SQL

How to Post Data/Code to get the best Help How to Post Performance Problems
Post #821199
Posted Wednesday, November 18, 2009 2:42 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Saturday, July 19, 2014 8:45 AM
Points: 531, Visits: 2,078
I noticed that 'vliet' in a post earlier today seems to know about my binary approach calling it the "65, 33, 17, 9, 5, 3 an 2 spaces nested REPLACE construction".

I did notice one bug in the code I published. It appears to not work for strings of length K*2^N+1 for K>1 and 2^N+1 corresponding to the largest string substitution you are doing (33). Strings of this length would end up in a string of length 2 (e.g. 65, 97, 129, etc). Thus one additional replace(@s," "," ") is needed to handle these relatively rare cases. Here is the correct version for C#Screw because this might slow things down a nanosecond or so and put me in a different place in the leaderboard.

CREATE FUNCTION dbo.fn_CleanUp_MichaelMeierruth_MKII(@S VARCHAR(8000))
RETURNS VARCHAR(8000)
BEGIN
RETURN replace(replace(replace(replace(replace(replace(replace(ltrim(rtrim(@s)),
' ',' '),
' ',' '),
' ',' '),
' ',' '),
' ',' '),
' ',' '),
' ',' ')
END

I have also done some quick simulations to see maximum lengths of strings that can be handled with each value of N. These are the results so far:
N=1 10
N=2 38
N=3 286
N=4 4622
N=5 151534

Thus 2^N+1 = 33 for N=5 covers SQL Server's max length of 8000 quite nicely. No need for vliet's 65 for which I haven't figured out yet the maximum length string it could handle. From the above sequence it looks like something very big. Can anyone see the formula for this sequence?

OK. Thought about it a bit.
For N=6 it appears to be 9845678.
The formula seems to be 2^(N+1)+Product for N=0 to N of 2^N+1
E.g. for N=5 we get 64 + 33*17*9*5*3*2 = 151534
Post #821209
Posted Wednesday, November 18, 2009 3:08 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 4:13 AM
Points: 225, Visits: 643
Michael,
here you are now (included change to c# version):

Slick T-SQL version to strip Spaces and TABS anyone?

Time to sleep here shortly in UK ...

Thanks
Screw

10k rows
Each execution completed 10 times.

SQL function: Replace Technique : but using replacement chars ||*9*9||
2638
SQL function :Brigzy (C#Screw) Looping
2511
SQL function: Jeff Original : single bell char
1866
SQL function: Michael Meierruth Original
321
CLR: Brigzy (C#Screw) Looping
316
SQL function: Michael Meierruth III - extra replace
306

SQL function: Michael Meierruth : with hard coded spaces
305
CLR: Not looping using ||*9*9|| technique in C#
287
CLR: using Jeff's single char technique in C#
248
CLR: C#Screw II - Spaces and TABS
181
CLR: Flo - Spaces and TABS - v.compact
167
CLR :using JCBnew space replacer C#
160
CLR: Michael Meierruth - C#Screw conversion: with extra Replace
149

CLR: Michael Meierruth - C#Screw conversion: hard coded spaces
145
CLR: using Flo s space replacer C#
109
CLR: using Paul White space replacer C#
104


C# Gnu
____________________________________________________


Multi user data edit / data transfer Excel Task based solution
Version 2.7 released June 2013
Post #821234
« Prev Topic | Next Topic »

Add to briefcase «««1314151617»»»

Permissions Expand / Collapse