REPLACE Multiple Spaces with One

  • You are welcome Miles,

    I honestly only came across this a few months ago myself. I try to take portions of my code and revamp them based on new available functionality or methods I had missed to improve performance and size. I too would do loops myself until I came across this and laughed at myself when I discovered it had been there since Framework 2.0.

  • Antares686 (8/22/2012)


    nguyenthanh5 (8/20/2012)


    Hi Jeff,

    Sorry I missed the point.

    I have created a function in VB.NET to do just that. I tested and it's fast. Could you care to test it? I do not know if it is worth to make it CRL function. I do not know how to make CRL function in SQL yet. Below is my function:

    Public Function ReplaceSpacesWithOne(ByVal dataIn As String) As String

    ' Imports system.text to use StringBuilder '( this line before your Class or Module)

    ' use space(1) to avoid typo, make sure one one space

    Dim data() As String = dataIn.Split(Space(1))

    Dim Result As New StringBuilder

    data = dataIn.Split(Space(1))

    For Each Str As String In data

    If Not String.IsNullOrEmpty(Str) Then

    Result.Append(Str + Space(1))

    End If

    Next

    Return Result.ToString

    End Function

    I use tab to space out to make it easy to read, but after post the tab and space lost. Hope it's not hard to read.

    Best regards,

    Tom N

    The key difference is the Replace method is in native T-SQL which can be done in any version of SQL Server. As for the above I would personally simplify to the below (C# example).

    public string ReplaceSpacesWithOne(string strIn)

    {

    return string.Join(" ", strIn.Split(new char[] { ' ' }, StringSplitOptions.RemoveEmptyEntries));

    }

    Thanking you.

    Use String.join together with Split make the code to online! CLR can be used on SQL Server 2005 or later.

    VB.Net is verbal compares to C#. You inspire me to start using C#. (the trouble is my team mates use VB.net).

    VB.Net:

    Public Shared Function ReplaceSpacesBy1(ByVal dataIn As String) As String

    ReplaceSpacesBy1 = String.Join(Space(1), dataIn.Split(New Char() {Space(1)}, StringSplitOptions.RemoveEmptyEntries))

    End Function

    Jeff,

    I have dll with two functions - one with a loop and one without loop (Antares686's idea) and the class file but do not know how to attach to the post!

    Best Regards,

    Tom N

  • nguyenthanh5 (8/22/2012)


    Jeff,

    I have dll with two functions - one with a loop and one without loop (Antares686's idea) and the class file but do not know how to attach to the post!

    Best Regards,

    Tom N

    I don't actually want the DLL's, Tom. Build the CLR functions on SQL Server and then script them out. You can either post them using the [ code = "sql" ] IFCode shortcut to the left of a new message window or you can just paste them as plain text.

    Be sure to include the source code so someone else can have a look.

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

  • Jeff Moden (8/22/2012)


    nguyenthanh5 (8/22/2012)


    Jeff,

    I have dll with two functions - one with a loop and one without loop (Antares686's idea) and the class file but do not know how to attach to the post!

    Best Regards,

    Tom N

    I don't actually want the DLL's, Tom. Build the CLR functions on SQL Server and then script them out. You can either post them using the [ code = "sql" ] IFCode shortcut to the left of a new message window or you can just paste them as plain text.

    Be sure to include the source code so someone else can have a look.

    Here is the script and code.

    USE [databaseName]

    GO

    /****** Object: UserDefinedFunction [dbo].[ReplaceSpacesBy1SpaceS] Script Date: 08/23/2012 15:07:41 ******/

    CREATE FUNCTION [dbo].[ReplaceSpacesBy1SpaceS](@dataIn [nvarchar](4000))

    RETURNS [nvarchar](4000) WITH EXECUTE AS CALLER

    AS

    EXTERNAL NAME [CLRfunctions].[CLRfunctions.UserDefinedFunctions].[ReplaceSpacesBy1SpaceS]

    GO

    EXEC sys.sp_addextendedproperty @name=N'AutoDeployed', @value=N'yes' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'FUNCTION',@level1name=N'ReplaceSpacesBy1SpaceS'

    GO

    EXEC sys.sp_addextendedproperty @name=N'SqlAssemblyFile', @value=N'ReplaceSpacesBy1space.vb' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'FUNCTION',@level1name=N'ReplaceSpacesBy1SpaceS'

    GO

    EXEC sys.sp_addextendedproperty @name=N'SqlAssemblyFileLine', @value=10 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'FUNCTION',@level1name=N'ReplaceSpacesBy1SpaceS'

    source codes from VS 2008

    Imports System

    Imports System.Data

    Imports System.Data.SqlClient

    Imports System.Data.SqlTypes

    Imports Microsoft.SqlServer.Server

    Imports System.Text

    Partial Public Class UserDefinedFunctions

    <Microsoft.SqlServer.Server.SqlFunction()> _

    Public Shared Function ReplaceSpacesBy1SpaceS(ByVal dataIn As String) As SqlString

    ' short

    Return New SqlString(String.Join(Space(1), dataIn.Split(New Char() {" "c}, StringSplitOptions.RemoveEmptyEntries)))

    End Function

    Public Shared Function ReplaceSpacesBySpaceL(ByVal dataIn As String) As SqlString

    ' use imports system.text

    ' easy read and understand but long

    Dim data() As String = dataIn.Split(" "c)

    Dim Result As New StringBuilder

    For Each Str As String In data

    If Not String.IsNullOrEmpty(Str) Then

    Result.Append(Str + Space(1))

    End If

    Next

    Return New SqlString(Result.ToString.Trim())

    End Function

    End Class

    regards

    Tom N

  • My apologies for not being clear on a couple of things...

    First, I left spaces in the IFCodes I posted so they could be seen instead of working and I forgot to tell you that. Take the spaces out and magic will happen.

    Second, there's a way to generate the script for a CLR that will contain the compiled binary and that's what I was looking for. I just can't remember off the top of my head how to do it. I believe you have to gen the assembly itself?

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

  • Jeff Moden (8/23/2012)


    My apologies for not being clear on a couple of things...

    First, I left spaces in the IFCodes I posted so they could be seen instead of working and I forgot to tell you that. Take the spaces out and magic will happen.

    Second, there's a way to generate the script for a CLR that will contain the compiled binary and that's what I was looking for. I just can't remember off the top of my head how to do it. I believe you have to gen the assembly itself?

    Thanking you Jeff,

    I'm a dud, take me long time to learn. I now can see IFCode shortcuts.

    I got VS2008 made the assembly and deployed to SQL Server for me (my 1st time!). The other way is to compile source code and the result is dll. Then we can create assembly from the dll like :

    CREATE ASSEMBLY CLRFunctions

    FROM 'c:\myTestStuff \mySpaces.dll'

    WITH PERMISSION_SET = SAFE; -- ref Create Assembly book online

    I do know how to generate script CLR function including assembly yet. I will post such script if I can make it.

    Thanks,

    Tom N

  • how about this...?

    base on the fact that we have only odd and even number...

    select '['+ rtrim(ltrim(replace(replace(replace(replace(' Homer Simpson is kewl ',' ','_'),'__',''),'_ ',' '),'_',' '))) +']'

  • how about this ?

    select '|'+ rtrim(ltrim(replace(replace(replace(replace(' Homer Simpson is kewl ',' ','_'),'__',''),'_ ',' '),'_',' '))) +'|'

    its a good thing we only have odd and even number... ;o)

  • This approach was published in a solution quite early in this discussion.

  • Stumbled across this thread with a Google search. (Amazing how all paths lead 'home'.)

    The elegance of the solution to this problem in SQL is amazing. Got a warm glow when I contemplated it's beauty. That quickly vanished as I smacked myself for not having figured it out on my own. 😉

    [font="Verdana"]Please don't go. The drones need you. They look up to you.[/font]
    Connect to me on LinkedIn

  • Thomas Abraham (5/22/2013)


    Stumbled across this thread with a Google search. (Amazing how all paths lead 'home'.)

    The elegance of the solution to this problem in SQL is amazing. Got a warm glow when I contemplated it's beauty. That quickly vanished as I smacked myself for not having figured it out on my own. 😉

    Thak you very much for the feedback and for stopping by. I hope you're talking about the "Nested Replace" solution and not the one in the article. As happens many times, the discussion led to a solution better than the one in the article. The introduction in the article has a link to the "Nested Replace" soultion.

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

  • I'm late to this party, but here's my entirely TSQL offering to remove duplicate spaces. Most of the code below has been not so shamelessly borrowed from Jeff Moden's work, especially the famous DelimitedSplit8K function which I have found to be one of the most useful tools in the toolbox.

    I have no idea how this compares to the other solutions but I thought I'd just throw it into the mix. It's based mostly off of some other variations I've developed from the DelimitedSplit8K function and I think it takes a different approach to the problem. Basically, it splits the string and then puts it back together again. Whether that's better or not I'll let the scorekeeper determine.

    /* Complete functions for creating a test set are listed here below the main function */

    /* Create some test data (run once) */

    DROP TABLE dbo.AlphaNumericWithSpaces

    SELECT * INTO dbo.AlphaNumericWithSpaces

    FROM [dbo].[CreateCsv8K2DimArrayRandomAlphaNumericWithSpaces](1000,50,8,20)

    /* Run just to see what the test data looks like */

    SELECT * FROM dbo.AlphaNumericWithSpaces AS s

    /* Since this is an itvf you can use cross apply */

    SELECT

    CleanString

    FROM

    dbo.AlphaNumericWithSpaces AS s

    CROSS APPLY

    dbo.itvfRemoveDuplicateSpaces(s.CSV)

    The functions are below:

    /* This is the actual duplicate removal function */

    CREATE FUNCTION [dbo].[itvfRemoveDuplicateSpaces]

    (

    @pInputString VARCHAR(8000)

    )

    RETURNS TABLE WITH SCHEMABINDING AS

    RETURN

    WITH E1(N) AS (

    SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL

    SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL

    SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1

    ), --10E+1 or 10 rows

    E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows

    E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max

    cteTally(N) AS (SELECT 0 UNION ALL

    SELECT TOP (DATALENGTH(ISNULL(@pInputString,1))) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4

    ),

    cteStart(N1) AS (

    SELECT t.N+1

    FROM cteTally t

    WHERE (SUBSTRING(@pInputString,t.N,1) = ' ' OR t.N = 0)

    ),

    cteItem(Item) AS

    (

    SELECT Item = SUBSTRING(@pInputString,s.N1,ISNULL(NULLIF(CHARINDEX(' ',@pInputString,s.N1),0)-s.N1,8000))

    FROM cteStart s

    )

    SELECT

    STUFF(

    (SELECT

    COALESCE(NULLIF(' ' + i.Item,' '),NULL) AS [text()]

    FROM

    cteItem i

    FOR XML PATH('')),1,1,'')

    AS CleanString

    ;

    GO

    /* Create the test data function */

    /* NOTE: This is the correct function to use for this example. Thanks to Michael Meierruth for spotting the error. */

    CREATE FUNCTION [dbo].[CreateCsv8K2DimArrayRandomAlphaNumericWithSpaces]

    (

    @pNumberOfRows INT,

    @pNumberOfElementsPerRow INT,

    @pMinElementwidth INT,

    @pMaxElementWidth INT

    )

    RETURNS TABLE

    AS

    RETURN

    SELECT TOP (@pNumberOfRows) --Controls the number of rows in the test table

    ISNULL(ROW_NUMBER() OVER (ORDER BY(SELECT NULL)),0) AS RowNum,

    CSV =

    (--==== This creates each CSV

    SELECT CAST(

    STUFF(

    (

    SELECT TOP (@pNumberOfElementsPerRow) --Controls the number of CSV elements in each row

    ' '

    + dbo.svfGenerateUniqueCode(@pMinElementwidth,@pMaxElementWidth,' ')

    + '|'

    + dbo.svfGenerateUniqueCode(@pMinElementwidth,@pMaxElementWidth,' ')

    + REPLICATE(' ',(ABS(CONVERT(INT,CONVERT(VARBINARY(100),(SELECT MyNewID FROM dbo.iFunction))))/10000000)/10)

    FROM sys.All_Columns ac3 --Classic cross join pseudo-cursor

    CROSS JOIN sys.All_Columns ac4 --can produce row sets up 16 million.

    WHERE ac3.Object_ID <> ac1.Object_ID --Without this line, all rows would be the same.

    FOR XML PATH('')

    )

    ,1,1,'')

    AS VARCHAR(8000))

    )

    FROM sys.All_Columns ac1 --Classic cross join pseudo-cursor

    CROSS JOIN sys.All_Columns ac2 --can produce row sets up 16 million rows

    GO

    /* Used by the test data function to generate random values */

    CREATE FUNCTION [dbo].[svfGenerateUniqueCode]

    (

    @CodeMinLength INT

    ,@CodeMaxLength INT

    ,@SpecialChar VARCHAR(50)

    )

    RETURNS VARCHAR(100)

    AS

    BEGIN

    DECLARE @Code VARCHAR(100)

    DECLARE @CodeData TABLE

    (

    CodeChar VARCHAR(1)

    )

    DECLARE @Num TABLE

    (

    Digit INT NOT NULL

    PRIMARY KEY CLUSTERED

    )

    IF @CodeMaxLength <= @CodeMinLength

    SET @CodeMaxLength = @CodeMinLength + 1

    INSERT INTO @Num

    (

    Digit

    )

    SELECT

    Digit = 0

    UNION ALL

    SELECT

    Digit = 1

    UNION ALL

    SELECT

    Digit = 2

    UNION ALL

    SELECT

    Digit = 3

    UNION ALL

    SELECT

    Digit = 4

    UNION ALL

    SELECT

    Digit = 5

    UNION ALL

    SELECT

    Digit = 6

    UNION ALL

    SELECT

    Digit = 7

    UNION ALL

    SELECT

    Digit = 8

    UNION ALL

    SELECT

    Digit = 9

    ORDER BY

    1

    INSERT INTO @CodeData

    (

    CodeChar

    )

    SELECT

    CodeChar = SUBSTRING(b.Characters,a.RAND_INT % b.MOD,1)

    FROM

    (

    SELECT

    aa.Number

    ,RAND_INT = ABS(CONVERT(INT,CONVERT(VARBINARY(100),(SELECT MyNewID FROM dbo.iFunction))))

    FROM

    (

    SELECT Number = a.Digit + (b.Digit * 10) FROM @Num a CROSS JOIN @Num b

    ) aa

    ) a

    CROSS JOIN

    (

    SELECT

    MOD = LEN(bb.Characters) - 1

    ,bb.Characters

    FROM

    (

    SELECT

    Characters =

    'ABCDEFGHJKLMNPQURSUVWXYZ'

    + 'abcdefghjkmnpqursuvwxyz'

    + '0123456789'

    + @SpecialChar

    ) bb

    ) b

    ORDER BY

    (SELECT MyNewID FROM dbo.iFunction)

    SELECT

    @Code = ''

    SELECT

    @Code = @Code + CodeChar

    FROM

    @CodeData

    SELECT

    @Code =

    -- Random length from MIN to MAX Characters

    SUBSTRING(@Code,1,@CodeMinLength + (ABS(CONVERT(INT,CONVERT(VARBINARY(100),(SELECT MyNewID FROM dbo.iFunction))))) % (@CodeMaxLength - @CodeMinLength + 1))

    SET @Code = NULLIF(LTRIM(RTRIM(@Code)),'')

    RETURN @Code

    END

    GO

    CREATE VIEW [dbo].[iFunction] AS

    /**********************************************************************************************************************

    Purpose:

    This view is callable from UDF's which allows us to indirectly get a NEWID() within a function where we can't do such

    a thing directly in the function. This view also solves the same problem for GETDATE().

    Usage:

    SELECT MyNewID FROM dbo.iFunction; --Returns a GUID

    SELECT MyDate FROM dbo.iFunction; --Returns a Date

    Revision History:

    Rev 00 - 06 Jun 2004 - Jeff Moden - Initial creation

    Rev 01 - 06 Mar 2011 - Jeff Moden - Formalize code. No logic changes.

    **********************************************************************************************************************/

    SELECT MyNewID = NEWID(),

    MyDate = GETDATE();

    GO

     

  • Where/what is this iFunction?

    What's the difference between CreateCsv8K2DimArrayRandomAlphaNumeric and CreateCsv8K2DimArrayRandomAlphaNumericWithSpaces?

  • Michael Meierruth (5/26/2013)


    Where/what is this iFunction?

    What's the difference between CreateCsv8K2DimArrayRandomAlphaNumeric and CreateCsv8K2DimArrayRandomAlphaNumericWithSpaces?

    Oooops. :blush:

    I have re-posted the correct function in the post above. The difference is that the correct "...WithSpaces" version of the function adds random groups of 2 or more spaces into the text since replacing excess spaces is what we are trying to do. Sorry to have caused confusion.

    And the iFunction is another "Modenism" to allow the use of certain operators that won't usually work inside functions. Brilliant idea and one I use so routinely I simply forgot about it!

    CREATE VIEW [dbo].[iFunction] AS

    /**********************************************************************************************************************

    Purpose:

    This view is callable from UDF's which allows us to indirectly get a NEWID() within a function where we can't do such

    a thing directly in the function. This view also solves the same problem for GETDATE().

    Usage:

    SELECT MyNewID FROM dbo.iFunction; --Returns a GUID

    SELECT MyDate FROM dbo.iFunction; --Returns a Date

    Revision History:

    Rev 00 - 06 Jun 2004 - Jeff Moden - Initial creation

    Rev 01 - 06 Mar 2011 - Jeff Moden - Formalize code. No logic changes.

    **********************************************************************************************************************/

    SELECT MyNewID = NEWID(),

    MyDate = GETDATE();

    GO

  • Jeff Moden (5/25/2013)


    Thak you very much for the feedback and for stopping by. I hope you're talking about the "Nested Replace" solution and not the one in the article. As happens many times, the discussion led to a solution better than the one in the article. The introduction in the article has a link to the "Nested Replace" soultion.

    I was indeed talking about the nested replace solution. I marveled at the technique of the approach, the use two place pattern that reduces a problem of unknown size to a problem of a two char pattern repeated an unknown number of times. Once one is able to make that leap, the rest just snaps into place. Thanks Jeff.

    [font="Verdana"]Please don't go. The drones need you. They look up to you.[/font]
    Connect to me on LinkedIn

Viewing 15 posts - 391 through 405 (of 425 total)

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