REPLACE Multiple Spaces with One

Question

REPLACE Multiple Spaces with One

Viewing 15 posts - 196 through 210 (of 425 total)

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

Paul White SSC Guru Points: 150468 More actions · Answer 1

Right that's me for tonight - I'm off to the other end of the North Island this weekend so won't be online much, but I'll try to check by in the evenings (NZ time!). Have a great weekend everyone.

Paul

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

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

Michael Meierruth (11/20/2009)
C#Screw,
I use your method. But knowing that this not always reliable, I generally run a test several times until it 'settles down' to a pretty constant value. IMO it's the most important measurement, i.e. how long an end user has to wait for something. The rest is just statistics.

Hi Michael,

test several times

yep we running everyones example 10x and averaging result at the moment

I think DBA folk are interested in CPU time to see if the server is being strained? but that could be seen by looking at CPU Percentage utilisation too maybe.

Paul - it would be excellent if we could get CPU Percentage utilisation that into your stats query some how?

Ps What does IMO stand for ...

pps I thought yesterday "the great Space Race" was over but just a bit more on the stats would seem quite cool. I think especially (for my interest) to see how c# CLR compares.

C# Gnu
____________________________________________________

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

Michael Meierruth SSChampion Points: 10051 More actions · Answer 3

Michael Meierruth

SSChampion

Points: 10051

November 20, 2009 at 3:34 am

#1082073

IMO is short for In My Opinion.

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

Michael Meierruth (11/20/2009)
IMO is short for In My Opinion.

SIAN

C# Gnu
____________________________________________________

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

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

SIAN

means Sorry I Am Nube:-)

C# Gnu
____________________________________________________

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

Michael Meierruth SSChampion Points: 10051 More actions · Answer 6

C#Screw

There are more or less 17 different means of 'Nube', one of which is 'new'. So this is what I'm assuming. You might want to look up the others - for some laughter.

Michael Meierruth SSChampion Points: 10051 More actions · Answer 7

Regarding collation, what I seem to be discovering is that any SQL Server 2000 database whose collation is different from the instance default will cause Jeff's code NOT to work.

I have tried do add the collate syntax in different areas of Jeff's code - to no avail.

All seems to be well on SQL Server 2005.

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

Results:

Firstly : sorry Jeff : I spotted an error in earlier SQL, script was actually calling Recusive solution but displaying your name, that explains why you were in front of looping/recursive when no double spaces in the data.

Here are the detailed results when there are NO double spaces in the data

(using Pauls stats lookup SQL, results with ~~lost~~ lots of spaces will follow in next post)

Nube I learnt from my 10 yr son - thats what he calls me 'Your such a nube Dad!'

Hope this image shows up ok, Edit: how do you get image to display in-line in post?

C# Gnu
____________________________________________________

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

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

Results :

Attached are results (using Pauls stats SQL)

when there are LOTS of spaces:

(shame it won't display in-line?)

C# Gnu
____________________________________________________

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

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

Hi

collation details :

Test database default SQL_Latin1_General_CP1_CI_AS

TempDB default Latin1_General_CI_AS

Master database default Latin1_General_CI_AS

Cheers

Screw

C# Gnu
____________________________________________________

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

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

Did you notice the BIG difference between Logical reads between SQL and CLR solutions?

:w00t:

Edit : Any Microsoft folk on this thread?

C# Gnu
____________________________________________________

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

Michael Meierruth SSChampion Points: 10051 More actions · Answer 12

Finally found a solution to this collate problem.

In Jeff's code if you change the line

LTRIM(RTRIM(OriginalString))

to

LTRIM(RTRIM(OriginalString collate your_database_collation_name))

the code starts to work. You must specify the collation of the database you're in (only if it is different from the instance collation).

Yes, it's a bit strange!

Just to be sure, I tried the opposite in SQL Server 2005, i.e. I specified a collation different from the instance collation and different from the database collation. It all seems to work.

Now let's tackle Paul's performance problem when using different collations. Thus this will need to be done on SQL Server 2005.

Michael Meierruth SSChampion Points: 10051 More actions · Answer 13

C# Screw (11/20/2009)
Hi
collation details :
Test database default SQL_Latin1_General_CP1_CI_AS
TempDB default Latin1_General_CI_AS
Master database default Latin1_General_CI_AS
Cheers
Screw

Are on SQL Server 2000? If so, Jeff's code should fail with this. Please try Jeff's code from the original article.

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

Hi Michael

I am 2005 here, using folowing for Jeff

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

C# Gnu
____________________________________________________

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

Jeff Moden SSC Guru Points: 1004441 More actions · Answer 15

Michael Meierruth (11/20/2009)
Regarding collation, what I seem to be discovering is that any SQL Server 2000 database whose collation is different from the instance default will cause Jeff's code NOT to work.
I have tried do add the collate syntax in different areas of Jeff's code - to no avail.
All seems to be well on SQL Server 2005.

I'm not having any problems with changing the collation on any of the T-SQL examples... you just need to put it in right after the "test string".

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