InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 22 May 2013 18:00:54 GMT20http://www.sqlservercentral.com/Forums/Topic819042-203-1.aspxStumbled 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. ;-)Wed, 22 May 2013 13:59:18 GMTThomas Abrahamhttp://www.sqlservercentral.com/Forums/Topic819042-203-1.aspxThis approach was published in a solution quite early in this discussion.Mon, 28 Jan 2013 01:37:35 GMTMichael Meierruthhttp://www.sqlservercentral.com/Forums/Topic819042-203-1.aspxhow about this ?[code="sql"]select '|'+ rtrim(ltrim(replace(replace(replace(replace(' Homer Simpson is kewl ',' ','_'),'__',''),'_ ',' '),'_',' '))) +'|'[/code]its a good thing we only have odd and even number... ;o)Wed, 23 Jan 2013 20:01:19 GMTfranky.chhttp://www.sqlservercentral.com/Forums/Topic819042-203-1.aspxhow 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 ',' ','_'),'__',''),'_ ',' '),'_',' '))) +']'Wed, 23 Jan 2013 19:50:10 GMTfranky.chhttp://www.sqlservercentral.com/Forums/Topic819042-203-1.aspx[quote][b]Jeff Moden (8/23/2012)[/b][hr]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?[/quote]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 CLRFunctionsFROM 'c:\myTestStuff \mySpaces.dll'WITH PERMISSION_SET = SAFE; -- ref Create Assembly book onlineI do know how to generate script CLR function including assembly yet. I will post such script if I can make it.Thanks,Tom NThu, 23 Aug 2012 22:52:19 GMTnguyenthanh5http://www.sqlservercentral.com/Forums/Topic819042-203-1.aspxMy 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?Thu, 23 Aug 2012 19:13:29 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic819042-203-1.aspx[quote][b]Jeff Moden (8/22/2012)[/b][hr][quote][b]nguyenthanh5 (8/22/2012)[/b][hr]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[/quote]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.[/quote]Here is the script and code. [code="sql"]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 CALLERAS EXTERNAL NAME [CLRfunctions].[CLRfunctions.UserDefinedFunctions].[ReplaceSpacesBy1SpaceS]GOEXEC sys.sp_addextendedproperty @name=N'AutoDeployed', @value=N'yes' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'FUNCTION',@level1name=N'ReplaceSpacesBy1SpaceS'GOEXEC sys.sp_addextendedproperty @name=N'SqlAssemblyFile', @value=N'ReplaceSpacesBy1space.vb' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'FUNCTION',@level1name=N'ReplaceSpacesBy1SpaceS'GOEXEC sys.sp_addextendedproperty @name=N'SqlAssemblyFileLine', @value=10 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'FUNCTION',@level1name=N'ReplaceSpacesBy1SpaceS'[/code]source codes from VS 2008[code="vb"]Imports SystemImports System.DataImports System.Data.SqlClientImports System.Data.SqlTypesImports Microsoft.SqlServer.ServerImports System.TextPartial 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 FunctionEnd Class[/code]regardsTom NWed, 22 Aug 2012 23:31:07 GMTnguyenthanh5http://www.sqlservercentral.com/Forums/Topic819042-203-1.aspx[quote][b]nguyenthanh5 (8/22/2012)[/b][hr]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[/quote]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.Wed, 22 Aug 2012 19:41:07 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic819042-203-1.aspx[quote][b]Antares686 (8/22/2012)[/b][hr][quote][b]nguyenthanh5 (8/20/2012)[/b][hr]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 FunctionI 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[/quote]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).[code]public string ReplaceSpacesWithOne(string strIn){ return string.Join(" ", strIn.Split(new char[] { ' ' }, StringSplitOptions.RemoveEmptyEntries));}[/code][/quote]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:[code]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[/code]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 NWed, 22 Aug 2012 18:52:03 GMTnguyenthanh5http://www.sqlservercentral.com/Forums/Topic819042-203-1.aspxYou 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.Wed, 22 Aug 2012 11:35:08 GMTAntares686http://www.sqlservercentral.com/Forums/Topic819042-203-1.aspxAntares686 - thanks I have used the loops for years and this helps clarify and is cleaner.m.Wed, 22 Aug 2012 09:37:22 GMTMiles Nealehttp://www.sqlservercentral.com/Forums/Topic819042-203-1.aspx[quote][b]nguyenthanh5 (8/20/2012)[/b][hr]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 FunctionI 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[/quote]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).[code]public string ReplaceSpacesWithOne(string strIn){ return string.Join(" ", strIn.Split(new char[] { ' ' }, StringSplitOptions.RemoveEmptyEntries));}[/code]Wed, 22 Aug 2012 06:50:21 GMTAntares686http://www.sqlservercentral.com/Forums/Topic819042-203-1.aspx[quote][b]nguyenthanh5 (8/21/2012)[/b][hr]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.TrimBest Regards,Tom N[/quote]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.Tue, 21 Aug 2012 21:42:39 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic819042-203-1.aspxHi 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.TrimBest Regards,Tom NTue, 21 Aug 2012 00:46:09 GMTnguyenthanh5http://www.sqlservercentral.com/Forums/Topic819042-203-1.aspx[quote][b]nguyenthanh5 (8/20/2012)[/b][hr]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 FunctionI 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[/quote]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.Mon, 20 Aug 2012 22:48:27 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic819042-203-1.aspxHi 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 FunctionI 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 NMon, 20 Aug 2012 19:08:12 GMTnguyenthanh5http://www.sqlservercentral.com/Forums/Topic819042-203-1.aspxVery well thought out I have to say, and it can be applied elsewhere. Good job.Mon, 20 Aug 2012 08:25:22 GMTAntares686http://www.sqlservercentral.com/Forums/Topic819042-203-1.aspx[quote][b]nguyenthanh5 (8/19/2012)[/b][hr]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[/quote]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....[code="sql"] SELECT SomeString = REPLACE(SomeString,' ','') FROM dbo.Sometable[/code]Mon, 20 Aug 2012 05:16:41 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic819042-203-1.aspxHi 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 NSun, 19 Aug 2012 20:02:46 GMTnguyenthanh5http://www.sqlservercentral.com/Forums/Topic819042-203-1.aspx[quote][b]C# Gnu (8/19/2012)[/b][hr]If I created such a page - what kind of things would you like to see?[/quote]Links to my two articles for the rapid creation of test data? :-D[url]http://www.sqlservercentral.com/articles/Data+Generation/87901/[/url][url]http://www.sqlservercentral.com/articles/Test+Data/88964/[/url][quote]How would you like tests to be designed / what performance indicators would you like besides overall execution time?[/quote]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.[url]http://www.sqlservercentral.com/articles/T-SQL/91724/[/url]Sun, 19 Aug 2012 12:40:29 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic819042-203-1.aspx[quote][b]TheSQLGuru (8/19/2012)[/b][hr]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))?[/quote]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.Sun, 19 Aug 2012 12:32:51 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic819042-203-1.aspxJeff, 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))?Sun, 19 Aug 2012 08:28:20 GMTTheSQLGuruhttp://www.sqlservercentral.com/Forums/Topic819042-203-1.aspx[quote][b]Jeff Moden (8/19/2012)[/b][hr][quote][b]res.manish (8/18/2012)[/b][hr]what rubbish, you could use Stuff() instead of using complicated methods.simply...[/quote]What's absolute rubbish is when someone makes such a comment without any proof. Let's see your code and we'll find out.[/quote]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 ;)Sun, 19 Aug 2012 07:12:54 GMTC# Gnuhttp://www.sqlservercentral.com/Forums/Topic819042-203-1.aspx[quote][b]Dave Pendleton (8/17/2012)[/b][hr]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.[/quote]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).Sun, 19 Aug 2012 07:02:49 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic819042-203-1.aspx[quote][b]CELKO (8/18/2012)[/b][hr]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) were 1)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.[/quote]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.Sun, 19 Aug 2012 07:00:33 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic819042-203-1.aspx[quote][b]balaji.v (8/17/2012)[/b][hr]Hi Folks,How about adding double byte spaces to a Chinese character string?Say i have a string of length 20. I want it to be of length 30. So i add SPACE(10)This string is full of Chinese characters (big5). But the SPACE(10) is not. Now when i read the output through a chinese system/app, i get garbled characters/output as the space is not a double byte space. How would i go about solving this one? single byte space character to double byte space character.Thanks.BV[/quote]I haven't tried it but I suspect it's just a collation problem between systems. Try using COLLATE with the particular collation you're using on the Chinese system. Also make sure that the language settings are correct for Chinese characters when you do the concatenation.Sun, 19 Aug 2012 06:57:23 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic819042-203-1.aspx[quote][b]res.manish (8/18/2012)[/b][hr]what rubbish, you could use Stuff() instead of using complicated methods.simply...[/quote]What's absolute rubbish is when someone makes such a comment without any proof. Let's see your code and we'll find out.Sun, 19 Aug 2012 06:51:46 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic819042-203-1.aspxThis 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) were 1)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.Sat, 18 Aug 2012 10:50:35 GMTCELKOhttp://www.sqlservercentral.com/Forums/Topic819042-203-1.aspxwhat rubbish, you could use Stuff() instead of using complicated methods.simply...Sat, 18 Aug 2012 01:25:41 GMTres.manishhttp://www.sqlservercentral.com/Forums/Topic819042-203-1.aspxI'm worried by your comment "not so common collation Latin1_general_ci_ai". Whilst new to SQL some years ago I installed a third party system that recommended this collation. I have been using it as the default ever since (15 years or so). If this is the not so common, what is the common and should I change my default?Sat, 18 Aug 2012 00:54:26 GMTTheSpyderhttp://www.sqlservercentral.com/Forums/Topic819042-203-1.aspxPerhaps 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.Fri, 17 Aug 2012 22:27:45 GMTDave Pendletonhttp://www.sqlservercentral.com/Forums/Topic819042-203-1.aspx[quote][b]Dave Pendleton (8/17/2012)[/b][hr]I use Jeff's method all the time in SSIS, except I use a vertical tab as the "unlikely" character.[code="plain"]REPLACE(REPLACE(REPLACE(LTRIM(«Column»)," "," " + "\v"),"\v" + " ",""),"\v","")[/code][/quote]Thank you for the support but the method is an order of magnitude slower than the method found in the discussion. Please see the beginning of the article for an update and which thread to see for an even faster method than mine.Fri, 17 Aug 2012 22:23:45 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic819042-203-1.aspxOk, cool. I've tended to use two strange characters (ones that can't be typed easily), to avoid the chance of hitting a match. But I agree that in normal text (which is where double-spaces might matter), a vertical space is probably never going to appear.Fri, 17 Aug 2012 21:15:54 GMTRob Farleyhttp://www.sqlservercentral.com/Forums/Topic819042-203-1.aspxActually Rob, you will see stuff like that in HL7 data all the time.I've used the vertical tab for billions of rows over the years; it has never failed.Fri, 17 Aug 2012 21:07:16 GMTDave Pendletonhttp://www.sqlservercentral.com/Forums/Topic819042-203-1.aspxCan't this be done in a much more simple way than trying to pick a single character that could never appear? By choosing two characters that can't appear next to each other (and sure, use 'bell' and vertical tab, or whatever), the chance of breaking it is far less.SELECT REPLACE(REPLACE(REPLACE(OriginalString, ' ', '~|'), '|~', ''), '~|', ' ')FROM @Demo;Fri, 17 Aug 2012 20:32:39 GMTRob Farleyhttp://www.sqlservercentral.com/Forums/Topic819042-203-1.aspxHi Folks,How about adding double byte spaces to a Chinese character string?Say i have a string of length 20. I want it to be of length 30. So i add SPACE(10)This string is full of Chinese characters (big5). But the SPACE(10) is not. Now when i read the output through a chinese system/app, i get garbled characters/output as the space is not a double byte space. How would i go about solving this one? single byte space character to double byte space character.Thanks.BVFri, 17 Aug 2012 20:23:19 GMTbalaji.vhttp://www.sqlservercentral.com/Forums/Topic819042-203-1.aspxI use Jeff's method all the time in SSIS, except I use a vertical tab as the "unlikely" character.[code="plain"]REPLACE(REPLACE(REPLACE(LTRIM(«Column»)," "," " + "\v"),"\v" + " ",""),"\v","")[/code]Fri, 17 Aug 2012 18:13:27 GMTDave Pendletonhttp://www.sqlservercentral.com/Forums/Topic819042-203-1.aspxDECLARE @myString varchar(500)='select * from gdgdfnkdngjrwrwrrwkg;select tuyututuut;'SELECT REPLACE( REPLACE( REPLACE( LTRIM(RTRIM(@myString)),' ',' '+CHAR(7)) ,CHAR(7)+' ','') ,CHAR(7),'') SQL can do it with out the help of C#.. :cool: Happy Coding.. :-)Wed, 04 Apr 2012 02:35:43 GMTmailatunnihttp://www.sqlservercentral.com/Forums/Topic819042-203-1.aspxHi All,I really like the REPLACE method and have used it successfully as follows. The REPLACE functions can either be nested (harder to maintain) or executed serially (my preference) as shown. This is definitely a CBE (crude but effective) method. I like it's simplicity, versatility, scalability and maintainability.-- Remove special characters (<h-tab>, <lf>, <cr>, ") and compress multiple spaces to one space character.-- 09 = Horizontal Tabupdate Definitions set ObjectText = replace(ObjectText, CHAR(09), ' ')-- 10 = Line Feedupdate Definitions set ObjectText = replace(ObjectText, CHAR(10), ' ')-- 13 = Carriage Returnupdate Definitions set ObjectText = replace(ObjectText, CHAR(13), ' ')-- 34 = " Double quote.update Definitions set ObjectText = replace(ObjectText, CHAR(34), ' ')-- Convert each occurrence of ' ' (two spaces) to ' ' (one space) 8 times.-- This will compress space blocks of up to 256 (2^8) characters in length guaranteeing only one space between non-blank characters.update Definitions set ObjectText = replace(ObjectText, ' ', ' ')update Definitions set ObjectText = replace(ObjectText, ' ', ' ')update Definitions set ObjectText = replace(ObjectText, ' ', ' ')update Definitions set ObjectText = replace(ObjectText, ' ', ' ')update Definitions set ObjectText = replace(ObjectText, ' ', ' ')update Definitions set ObjectText = replace(ObjectText, ' ', ' ')update Definitions set ObjectText = replace(ObjectText, ' ', ' ')update Definitions set ObjectText = replace(ObjectText, ' ', ' ')Because SQL is so liberal regarding spaces between clauses and multi-part entity names, I also found myself using the following constructs after removing all special characters and converting every double-space to a single-space. Recompress spaces a couple of times after this because changing '] space' or 'space [' to space will leave two spaces in a row.-- '] .' Right square bracket followed by a space and a period changed to '.'.update Definitions set ObjectText = replace(ObjectText, '] .', '.')-- '. [' Left square bracket preceded by a period and a space changed to '.'.update Definitions set ObjectText = replace(ObjectText, '. [', '.')-- ' .' Space and a period changed to '.'.update Definitions set ObjectText = replace(ObjectText, ' .', '.')-- '. ' Period and a space changed to '.'.update Definitions set ObjectText = replace(ObjectText, '. ', '.')-- '].' Right square bracket followed by a period changed to '.'.update Definitions set ObjectText = replace(ObjectText, '].', '.')-- '.[' Period followed by a left square bracket changed to '.'.update Definitions set ObjectText = replace(ObjectText, '.[', '.')-- 91 = [ Left square bracket changed to a space.update Definitions set ObjectText = replace(ObjectText, '[', ' ')-- 93 = ] Right square bracket changed to a space.update Definitions set ObjectText = replace(ObjectText, ']', ' ')Thanks, Bob.Fri, 16 Dec 2011 10:10:12 GMTBob-599816http://www.sqlservercentral.com/Forums/Topic819042-203-1.aspx[quote][b]smartin108 (3/26/2011)[/b][hr]I say P-O-2 is more efficient because the number of maximum string size handled by each level of REPLACE is usually greater than in the 65, 33... series:N=1 (REPLACE x 2) 4N=2 (REPLACE x 3) 10N=3 (REPLACE x 4) 38N=4 (REPLACE x 5) 398N=5 (REPLACE x 6) 11806N=6 (REPLACE x 7) 754702[/quote]It is indeed more efficient. But maximum is a bit of a misnomer, thisenumbers are not the length of the longest string of spaces the algorithm will reduce to a single space but instead the number 1 less than the smallest number for which it doesn't work. For example N=2 works for 12, 13, and 16 but fails for 11 (and for 14 and for 15). Of course the maximum isn't really interesting, which is a pity, because it's easy to get a formula for it - the maximum string for which N=k works is 2 to the power 2k. The interesting number is the number you called the maximum (the algorithm is guaranteed to work for blocks of spaces of this and all smaller lengths), for which I haven't (yet) found a clean formula. I'll contimue to try.Tue, 29 Mar 2011 12:59:59 GMTL' Eomot Inversé