Post reply

REPLACE Multiple Spaces with One

  • November 20, 2009 at 3:28 am

    #1082071

    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
    SQLPerformance.com
    SQLkiwi blog
    @SQL_Kiwi

  • November 20, 2009 at 3:31 am

    #1082072

    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

  • November 20, 2009 at 3:34 am

    #1082073

    IMO is short for In My Opinion.

  • November 20, 2009 at 3:41 am

    #1082076

    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

  • November 20, 2009 at 4:02 am

    #1082083

    SIAN

    means Sorry I Am Nube:-)

    C# Gnu
    ____________________________________________________


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

  • November 20, 2009 at 4:31 am

    #1082096

    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.

  • November 20, 2009 at 4:35 am

    #1082097

    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.

  • November 20, 2009 at 4:38 am

    #1082100

    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

  • November 20, 2009 at 5:02 am

    #1082109

    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

  • November 20, 2009 at 5:21 am

    #1082117

    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

  • November 20, 2009 at 5:27 am

    #1082122

    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

  • November 20, 2009 at 5:32 am

    #1082125

    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.

  • November 20, 2009 at 5:40 am

    #1082127

    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.

  • November 20, 2009 at 5:45 am

    #1082128

    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(

    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

  • November 20, 2009 at 6:23 am

    #1082157

    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)
    Intro to Tally Tables and Functions

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

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