REPLACE Multiple Spaces with One

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

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

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'

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