REPLACE Multiple Spaces with One
-
CELKO (8/18/2012)
This was a topic many years ago and I think I saved it, but I cannot get to it now. The two best solutions (this is from memory) were1)REPLACE @in_string, ' ', '{}');
REPLACE @in_string, '}{ ', '');
REPLACE @in_string, '{}', ' ');
This is the same idea as the one given, but it almost doubles the length of the input string.
The next best one was nested REPLACES() calls with decreasing length strings of blanks whose sizes were a Fibonacci series. One poster tested it on a large sample and had the tinmings.
Correct. In fact, I posted the link to the post on this thread that shows the latter method and the timings are nearby. The performance of the latter method outstriped the method in the article by quite a bit which is why I made the link at the beginning of the article.
--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) -
Dave Pendleton (8/17/2012)
Perhaps so, but I want to do this in an SSIS expression, not SQL.And which method, specifically? I didn't read the entire thread. Are you talking about the CLR option? My data sources aren't always SQL Server.
I don't work with SSIS but the fastest method of doing this turned out to be the nested replaces that I provided a link to at the being of the article rather than the method that the article contains (which still beats the WHILE loop method by quite a bit).
--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/19/2012)
res.manish (8/18/2012)
what rubbish, you could use Stuff() instead of using complicated methods.simply...
What's absolute rubbish is when someone makes such a comment without any proof. Let's see your code and we'll find out.
Agreed!
Nice to see this thread being active again 🙂
I have been thinking about creating a web page for performance testing various SQL methods.
Allowing you to submit SQL for testing.
It seems a nice idea because then tests at least are being performed on the same box - and all should be fair!
If I created such a page - what kind of things would you like to see?
How would you like tests to be designed / what performance indicators would you like besides overall execution time?
Yea it's me C#Screw disguised as a Gnu 😉
C# Gnu
____________________________________________________
Excel Database Tasks : efficient business data processing
Version 3.3 released February 2015 -
Jeff, why do you include LTRIM(RTRIM(OriginalString)) in your code? Isn't that (potentially completely unnecessary) work for nothing because your remaining code would handle any leading/trailing spaces anyway? Did/can you do a test to see if the code is faster with/without LTRIM(RTRIM(OriginalString))?
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service -
TheSQLGuru (8/19/2012)
Jeff, why do you include LTRIM(RTRIM(OriginalString)) in your code? Isn't that (potentially completely unnecessary) work for nothing because your remaining code would handle any leading/trailing spaces anyway? Did/can you do a test to see if the code is faster with/without LTRIM(RTRIM(OriginalString))?My original code won't remove the "final" leading or trailing spaces if any are present in the original string. Multiple leading and trailing spaces would be reduced to just one but that left over leading or trailing space wouldn't be removed by the code.
--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) -
C# Gnu (8/19/2012)
If I created such a page - what kind of things would you like to see?Links to my two articles for the rapid creation of test data? 😀
http://www.sqlservercentral.com/articles/Data+Generation/87901/
http://www.sqlservercentral.com/articles/Test+Data/88964/
How would you like tests to be designed / what performance indicators would you like besides overall execution time?
It depends on the code. For example, Scalar UDF's are improperly rated when using SET STATISTICS. See the following article for more on that.
http://www.sqlservercentral.com/articles/T-SQL/91724/
--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) -
Hi all,
I think we can create CRL function in SQL Server and call it.
The code in VB.NET like this:
dim str as string = " a b c mmmmm xxx this cc that ... t."
str = str.replace(space(1),"")
Regards,
Tom N
-
nguyenthanh5 (8/19/2012)
Hi all,I think we can create CRL function in SQL Server and call it.
The code in VB.NET like this:
dim str as string = " a b c mmmmm xxx this cc that ... t."
str = str.replace(space(1),"")
Regards,
Tom N
Thanks Tom. Remember though, that the purpose of the article is to replace any number of spaces between non-space characters with just one space.
Also, you wouldn't need a CLR to remove all spaces like your code suggests. If that where the task, it could just as easily be done in T-SQL....
SELECT SomeString = REPLACE(SomeString,' ','')FROM dbo.Sometable
--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) -
Very well thought out I have to say, and it can be applied elsewhere. Good job.
-
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
-
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 point is that you wrote code that will replace all spaces with nothing. The point of the article was to turn multiple adjacent spaces into just one space.
So far as a CLR goes, that was done as a part of the testing that took place in the rest of this discussion.
--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) -
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
-
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.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) -
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));
}
-
Antares686 - thanks I have used the loops for years and this helps clarify and is cleaner.
m.
Not all gray hairs are Dinosaurs!
Viewing 15 posts - 376 through 390 (of 425 total)
You must be logged in to reply to this topic. Login to reply