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 «««3738394041»»

REPLACE Multiple Spaces with One Expand / Collapse
Author
Message
Posted Tuesday, August 21, 2012 9:42 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:43 PM
Points: 35,575, Visits: 32,167
nguyenthanh5 (8/21/2012)
Hi Jeff,

My function is replace multiple spaces with one space. I did tested it.

Look inside the For each loop, at the append statement: I append the data and a space immediately. So next append will have the space before. So the result string will remove multiple spaces with one space.

Because I add a space ready before next appending I now find out that I forgot to remove the last single space before return the result.

The return statement should be: Return Result.toString.Trim

Best Regards,

Tom N


The key here is that I missed the "split" function you used to isolate the non-space characters. After seeing that, I get it now.

Any chance of you converting this to an SQLCLR function in T-SQL and then scripting the function from SQL Server so I can try it out? Thanks, Tom.


--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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1348163
Posted Wednesday, August 22, 2012 6:50 AM
SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: Moderators
Last Login: Friday, November 14, 2014 10:06 AM
Points: 8,370, Visits: 745
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));
}




Post #1348351
Posted Wednesday, August 22, 2012 9:37 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, November 21, 2014 10:15 AM
Points: 2,464, Visits: 1,551
Antares686 - thanks I have used the loops for years and this helps clarify and is cleaner.

m.


Not all gray hairs are Dinosaurs!
Post #1348518
Posted Wednesday, August 22, 2012 11:35 AM
SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: Moderators
Last Login: Friday, November 14, 2014 10:06 AM
Points: 8,370, Visits: 745
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.



Post #1348597
Posted Wednesday, August 22, 2012 6:52 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, April 23, 2014 7:00 PM
Points: 6, Visits: 52
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
Post #1348816
Posted Wednesday, August 22, 2012 7:41 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:43 PM
Points: 35,575, Visits: 32,167
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1348820
Posted Wednesday, August 22, 2012 11:31 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, April 23, 2014 7:00 PM
Points: 6, Visits: 52
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
Post #1348857
Posted Thursday, August 23, 2012 7:13 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:43 PM
Points: 35,575, Visits: 32,167
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1349414
Posted Thursday, August 23, 2012 10:52 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, April 23, 2014 7:00 PM
Points: 6, Visits: 52
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
Post #1349454
Posted Wednesday, January 23, 2013 7:50 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, January 14, 2014 7:52 PM
Points: 2, Visits: 6
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 ',' ','_'),'__',''),'_ ',' '),'_',' '))) +']'
Post #1410875
« Prev Topic | Next Topic »

Add to briefcase «««3738394041»»

Permissions Expand / Collapse