REPLACE Multiple Spaces with One
-
Hi all
@jeff: Thanks for your feedback! But I have to admit, that I've been surprised about my first test results. Apparently I've made one failure I already did in our string splitting contest. The CLR function I've tried worked with NVARCHAR(MAX) and that's an absolute performance killer.
@brigzy:
For sure, if you want to add a C++ version, just do it :-). I'm not sure if many people will use XPs any more in SS2k5 but it would be an interesting comparison!
Some performance hints which appear to be constant in my SQLCLR tests:
* Since now, all CLR functions/procs I tested have always been faster with SqlString and SqlChars for the function interface instead of System.String and other native plain .NEt types.
* SqlChars appears to be even faster than SqlString but you have to ensure a correct length configuration by SqlFacet attribute (the failure I just did again).
* When working with SQLCLR types, get the values asap into plain .NET types, since those SQLCLR types are wrapper and become really slow when you work with them.
BTW: Your "fn_TidySpace_SingleChar_CLR" function removes all spaces in my tests. Maybe there is a little bug, but for the moment I have no time to debug it.
😀
Looks almost like the function I intended to build. I just used a second for-loop instead of a while-loop. for-loops are usually faster than while-loops since the compiler knows a specific end. At the moment I'm not sure if this is the main reason why my function is slightly faster 😎 than yours. I have to dive deeper to investigate this. (Probably this evening...)
So here's my (apparently fastest) C# function:
[Microsoft.SqlServer.Server.SqlFunction(
DataAccess=DataAccessKind.None,
IsDeterministic=true,
IsPrecise=true,
SystemDataAccess=SystemDataAccessKind.None)
]
public static SqlString fn_TidySpace_CharLoop_CLR(
[SqlFacet(
IsFixedLength=false,
MaxSize=4000)
]
SqlChars tidyMySpaces
)
{
// use input buffer as output buffer
char[] input = tidyMySpaces.Buffer;
int pos = 0;
int length = input.Length;
for (int i = 0; i < length; i++)
{
// get next character
char c = input;
// we reached a space
if (c == ' ')
{
// only write the space if our output stream position is NOT the
// current position.
if (pos != i)
input[pos] = c;
// loop to the end of spaces
for (; i < length; i++)
{
c = input;
// we reached the end of spaces, so break
if (c != ' ')
break;
}
// if we didn't reach the end of the input string get the current character
if (i < length)
input[++pos] = c;
}
else if (i != pos)
{
// only write input to output if we HAVE TO write
input[pos] = c;
}
// increase output positions
pos++;
}
return new string(input, 0, pos);
}
My test environment (I took Paul's and extended it to PRINT the current approach):
-- we do not test SSMS outputSET NOCOUNT ON;
GO
---============================================================================
-- Setup the test environment
---==============================================
-- Table with data to be cleaned
IF (OBJECT_ID('tempdb..#TestCleanSpaces') IS NOT NULL)
DROP TABLE #TestCleanSpaces;
GO
CREATE TABLE #TestCleanSpaces
(
Id INT NOT NULL IDENTITY(1,1)
PRIMARY KEY CLUSTERED
,Data VARCHAR(4000)
);
GO
---==============================================
-- create some test data
WITH random (r1, r2, r3) AS
(
SELECT
ABS(BINARY_CHECKSUM(NEWID()))
,ABS(BINARY_CHECKSUM(NEWID()))
,ABS(BINARY_CHECKSUM(NEWID()))
),
Numbers (Num) AS
(
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1))
FROM dbo.Numbers n1
CROSS JOIN dbo.Numbers n2
)
INSERT INTO #TestCleanSpaces (Data)
-- configure count of test data
SELECT TOP(100000)
REPLICATE(LEFT(CONVERT(NCHAR(36), NEWID()), r1 % 36) + SPACE(r2 % 20), r3 % 20)
FROM Numbers n1
CROSS JOIN random
---============================================================================
-- Execute the tests
DECLARE
@Bitbucket VARCHAR(4000),
@BitBucketUnicode NVARCHAR(4000);
---========================================================
-- fn_TidySpace_SingleChar_CLR
PRINT ('---========================================================');
PRINT ('-- CLR fn_TidySpace_SingleChar_CLR');
SET STATISTICS TIME ON;
SELECT @BitBucketUnicode = dbo.fn_TidySpace_SingleChar_CLR(Data)
FROM #TestCleanSpaces;
SET STATISTICS TIME OFF;
---========================================================
-- test Jeff's function
PRINT ('---========================================================');
PRINT ('-- Jeff''s function');
SET STATISTICS TIME ON;
SELECT @Bitbucket =
REPLACE(
REPLACE(
REPLACE(
LTRIM(RTRIM(Data))
,' ',' '+CHAR(7)) --Changes 2 spaces to the OX model
,CHAR(7)+' ','') --Changes the XO model to nothing
,CHAR(7),'')
FROM #TestCleanSpaces;
SET STATISTICS TIME OFF;
---========================================================
-- fn_TidySpace_CharLoop_CLR
PRINT ('---========================================================');
PRINT ('-- CLR fn_TidySpace_CharLoop_CLR');
SET STATISTICS TIME ON;
SELECT @BitBucketUnicode = dbo.fn_TidySpace_CharLoop_CLR(Data)
FROM #TestCleanSpaces;
SET STATISTICS TIME OFF;
---========================================================
-- SpaceReplacer CLR
PRINT ('---========================================================');
PRINT ('-- CLR SpaceReplacer');
SET STATISTICS TIME ON;
SELECT @BitBucketUnicode = dbo.SpaceReplacer(Data)
FROM #TestCleanSpaces;
SET STATISTICS TIME OFF;
GO
SELECT * FROM #TestCleanSpaces;
GO
My current results:
---========================================================
-- CLR fn_TidySpace_SingleChar_CLR
SQL Server Execution Times:
CPU time = 1344 ms, elapsed time = 1471 ms.
---========================================================
-- Jeff's function
SQL Server Execution Times:
CPU time = 2953 ms, elapsed time = 2985 ms.
---========================================================
-- CLR fn_TidySpace_CharLoop_CLR
SQL Server Execution Times:
CPU time = 422 ms, elapsed time = 506 ms.
---========================================================
-- CLR SpaceReplacer
SQL Server Execution Times:
CPU time = 453 ms, elapsed time = 458 ms.
Greets
Flo
-
Now including JCB (earlier post) and FLO's most recent c# submission, and now in order of exec time:
Beginning execution loop
Batch execution completed 10000 times.
SQL: using ||*9*9||
6390
SQL: Jeffs single char
3080
SQL:Looping
2883
CLR: looping
483
CLR: Not looping using ||*9*9|| technique in C#
293
CLR: Not looping using Jeff's single char technique in C#
253
CLR: using JCBnew space replacer C#
173
CLR: using Paul White space replacer C#
120
CLR: using Flo s space replacer C#
113
C# Gnu
____________________________________________________
Excel Database Tasks : efficient business data processing
Version 3.3 released February 2015 -
Hi C# Screw
Thanks for your testing! I'm curious about some additional test results.
Greets
Flo
-
Woah, lots of great stuff coming in here. I can hardly keep up. I keep refreshing to make sure what i am about to say has not been said and it has or has been superseded by other posts.
One thing that was mentioned briefly was that it would be really handy of SQL server natively supported regex. I had been looking at that to simplify some migration (and other random) tasks that involve email addresses and as an excercise in mucking about.
The post here has CLR regex functions for SQL. I haven't got round to trying them but if there is a simple regex for whitespace cleaning it could be an interesting one to add to the performance bundle.
http://justgeeks.blogspot.com/2008/08/adding-regular-expressions-regex-to-sql.html
[font="Arial"]_______________________________________________________
Change is inevitable... Except from a vending machine.[/font] -
Florian Reischl (11/18/2009)
Hi C# ScrewThanks for your testing! I'm curious about some additional test results.
Greets
Flo
Yes I am on a Tesco's laptop (2GB Core 2 Duo) SQL 2005 Standard.
I could post the assembly as CREATE ASSEMBY <assembly bits>
Then others could test - but I am worried to do so its asking people to install a dll
that they might not want to trust.
We spotted the bug earlier ... a misiing space from first c#replace ...
C# Gnu
____________________________________________________
Excel Database Tasks : efficient business data processing
Version 3.3 released February 2015 -
Hi Rob
Rob Fisk (11/18/2009)
Woah, lots of great stuff coming in here. I can hardly keep up. I keep refreshing to make sure what i am about to say has not been said and it has or has been superseded by other posts.One thing that was mentioned briefly was that it would be really handy of SQL server natively supported regex. I had been looking at that to simplify some migration (and other random) tasks that involve email addresses and as an excercise in mucking about.
The post here has CLR regex functions for SQL. I haven't got round to trying them but if there is a simple regex for whitespace cleaning it could be an interesting one to add to the performance bundle.
http://justgeeks.blogspot.com/2008/08/adding-regular-expressions-regex-to-sql.html%5B/quote%5D
I also thought about regex. Regex is a really powerful tool but it's more generic than fast for these specific tasks. We had another "short" discussion about SQLCLR functions to split strings here:
Performance issue with tally solution
We also tried some regex solutions and are quiet fast (faster than T-SQL approaches for more complex string manipulations) but hard coded solutions are way faster.
Greets
Flo
-
C# Screw (11/18/2009)
I could post the assembly as CREATE ASSEMBY <assembly bits>Then others could test - but I am worried to do so its asking people to install a dll
that they might not want to trust.
We spotted the bug earlier ... a misiing space from first c#replace ...
I'm not sure if people will install a binary without really knowing the inside but I think there are many people in here who use Visual Studio and probably will do their own tests.
Thanks
Flo
-
Florian Reischl (11/18/2009)
C# Screw (11/18/2009)
I could post the assembly as CREATE ASSEMBY <assembly bits>Then others could test - but I am worried to do so its asking people to install a dll
that they might not want to trust.
We spotted the bug earlier ... a misiing space from first c#replace ...
I'm not sure if people will install a binary without really knowing the inside but I think there are many people in here who use Visual Studio and probably will do their own tests.
Thanks
Flo
Ok here is all submitted c# so far :-
using System;using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.Text;
using Microsoft.SqlServer.Server;
public partial class UserDefinedFunctions
{
[SqlFunction()]
public static string fn_TidySpace_Looping_CLR(string CleanMeUpScottie)
{
while (CleanMeUpScottie.IndexOf(" ", 0) != -1)
{
CleanMeUpScottie = CleanMeUpScottie.Replace(" ", " ");
}
return CleanMeUpScottie;
}
[SqlFunction()]
public static string fn_TidySpace_NotLooping_CLR(string CleanMeUpScottie)
{
return CleanMeUpScottie.Trim().Replace(" ", " ||*9*9||")
.Replace("||*9*9|| ", "").Replace("||*9*9||", "");
}
[SqlFunction()]
public static String fn_TidySpace_SingleChar_CLR(string CleanMe)
{
return CleanMe.Trim().Replace(" ", " \a").Replace("\a ", "").Replace("\a", "");
}
[SqlFunction
(
DataAccess = DataAccessKind.None,
SystemDataAccess = SystemDataAccessKind.None,
IsDeterministic = true,
IsPrecise = true
)
]
[return: SqlFacet(MaxSize = 4000)]
public static SqlString fn_TidySpace_PW_CLR
(
[SqlFacet(IsNullable = false, MaxSize = 4000)] SqlChars Input
)
{
const char SPACE = ' ';
bool skipping = false; // In space-skipping mode
char[] input = Input.Buffer; // Reference to the internal char[] buffer of the SqlChars input
int length = input.Length; // Remember the length of the original
int writePos = 0; // Initialise the writing position
// Loop through each character
// scan = current scan position
// write = current write position
for (int readPos = 0; readPos < length; readPos++)
{
// Skip spaces if we are in 'skip mode' (see later)
if (skipping)
{
// Space skipping
while (readPos < length && input[readPos] == SPACE) { readPos++; }
// Bail if we ran out of string
if (readPos >= length) { break; }
}
// If the write position is before the scan position,
// the output string is no longer in sync with the input
// (we must have skipped spaces at some point).
// So, we need to copy the current character
// from the read position to the write position
if (writePos < readPos) { input[writePos] = input[readPos]; }
// Advance the write position
// (for all non-spaces, and for the first space in a sequence of spaces)
writePos++;
// Set or reset skip mode
skipping = (input[readPos] == SPACE);
}
// Return a string constructed from the part of
// the buffer we actually wrote to
return new SqlString(new string(input, 0, writePos));
}
[SqlFunction()]
public static string fn_TidySpace_JCB_CLR(string inData)
{
StringBuilder result = new StringBuilder();
int pos = 0;
int nextWriteStart = -1;
int length = inData.Length;
bool lastWasWhiteSpace = true;
bool buffering = false;
while (pos < length)
{
if (lastWasWhiteSpace && inData[pos] == ' ' && buffering)
{
result.Append(inData.Substring(nextWriteStart, (pos - nextWriteStart)));
nextWriteStart = -1;
buffering = false;
}
else if (nextWriteStart == -1 && (inData[pos] != ' ' || (!lastWasWhiteSpace && inData[pos] == ' ')))
{
nextWriteStart = pos;
buffering = true;
}
if (inData[pos] == ' ')
{
lastWasWhiteSpace = true;
}
else
{
lastWasWhiteSpace = false;
}
pos++;
}
if (buffering)
{
result.Append(inData.Substring(nextWriteStart, (pos - nextWriteStart)));
}
return result.ToString();
}
[Microsoft.SqlServer.Server.SqlFunction(
DataAccess = DataAccessKind.None,
IsDeterministic = true,
IsPrecise = true,
SystemDataAccess = SystemDataAccessKind.None)
]
public static SqlString fn_TidySpace_FLO_CLR(
[SqlFacet(
IsFixedLength = false,
MaxSize = 4000)
]
SqlChars tidyMySpaces
)
{
// use input buffer as output buffer
char[] input = tidyMySpaces.Buffer;
int pos = 0;
int length = input.Length;
for (int i = 0; i < length; i++)
{
// get next character
char c = input;
// we reached a space
if (c == ' ')
{
// only write the space if our output stream position is NOT the
// current position.
if (pos != i)
input[pos] = c;
// loop to the end of spaces
for (; i < length; i++)
{
c = input;
// we reached the end of spaces, so break
if (c != ' ')
break;
}
// if we didn't reach the end of the input string get the current character
if (i < length)
input[++pos] = c;
}
else if (i != pos)
{
// only write input to output if we HAVE TO write
input[pos] = c;
}
// increase output positions
pos++;
}
return new string(input, 0, pos);
}
}
and the SQL batch thats works with it :
--PREPARESET NOCOUNT ON
go
CREATE FUNCTION dbo.fn_CleanUp(@FooString VARCHAR(max))
RETURNS VARCHAR(max)
BEGIN
WHILE CHARINDEX(' ',@FooString) > 0
SELECT @FooString = REPLACE(@FooString,' ',' ')
RETURN @FooString
END
GO
CREATE TABLE #TEMP1 (COL1 VARCHAR(900))
CREATE TABLE #TEMP2 (COL2 VARCHAR(900), COL3 VARCHAR(900), COL4 VARCHAR(900),COL5 VARCHAR(900))
go
--INSERT 200k ROWS WITH RANDOM SPACES ON EACH TABLE, SEPARATE TABLES ARE USED TO AVOID CACHING, THIS MAY TAKE QUITE AWHILE
DECLARE @SPACECOUNT1 INT,@SPACECOUNT2 INT,@SPACECOUNT3 INT,@SPACECOUNT4 INT
SELECT @SPACECOUNT1 = CAST(CAST(NEWID() AS VARBINARY(1)) AS INT),@SPACECOUNT2 = CAST(CAST(NEWID() AS VARBINARY(1)) AS INT)
INSERT INTO #TEMP1 (COL1)
OUTPUT inserted.COL1 INTO #TEMP2 (COL2)
SELECT 'TEST1'+SPACE(@SPACECOUNT1)+'TEST2'+SPACE(@SPACECOUNT2)+'TEST3'
GO 10000
--select * FROM #TEMP1
--select * FROM #TEMP2
--SELECTS
--SET STATISTICS TIME ON
DECLARE @TheTime DATETIME
SELECT @TheTime= GETDATE()
UPDATE #TEMP2 SET COL3 = LTRIM(RTRIM(
REPLACE(REPLACE(REPLACE(COL2,' ',' ||*9*9||'),'||*9*9|| ',''),'||*9*9||','')
))
FROM #TEMP2
PRINT 'SQL: using ||*9*9||'
PRINT DATEDIFF(ms,@TheTime,GETDATE())
go
DECLARE @TheTime DATETIME
SELECT @TheTime= GETDATE()
UPDATE #TEMP2 SET COL3 =
REPLACE(
REPLACE(
REPLACE(
LTRIM(RTRIM(COL2)) ,' ',' '+CHAR(7)) --Changes 2 spaces to the OX model
,CHAR(7)+' ','') --Changes the XO model to nothing
,CHAR(7),'') --Changes the remaining X's to nothing
FROM #TEMP2
PRINT 'SQL: Jeffs single char'
PRINT DATEDIFF(ms,@TheTime,GETDATE())
go
go
DECLARE @TheTime DATETIME
SELECT @TheTime= GETDATE()
UPDATE #TEMP2 SET COL4= dbo.fn_CleanUp(COL2)
FROM #TEMP2
PRINT 'SQL:Looping'
PRINT DATEDIFF(ms,@TheTime,GETDATE())
go
DECLARE @TheTime DATETIME
SET @TheTime=GETDATE()
UPDATE #TEMP2 SET COL5= dbo.fn_TidySpace_Looping_CLR(COL2)
FROM #TEMP2
PRINT 'CLR: looping '
PRINT DATEDIFF(ms,@TheTime,GETDATE())
go
DECLARE @TheTime DATETIME
SET @TheTime=GETDATE()
UPDATE #TEMP2 SET COL5= dbo.fn_TidySpace_NotLooping_CLR(COL2)
FROM #TEMP2
PRINT 'CLR: Not looping using ||*9*9|| technique in C#'
PRINT DATEDIFF(ms,@TheTime,GETDATE())
go
DECLARE @TheTime DATETIME
SET @TheTime=GETDATE()
UPDATE #TEMP2 SET COL5= dbo.fn_TidySpace_SingleChar_CLR(COL2)
FROM #TEMP2
PRINT 'CLR: Not looping using Jeff''s single char technique in C#'
PRINT DATEDIFF(ms,@TheTime,GETDATE())
Go
DECLARE @TheTime DATETIME
SET @TheTime=GETDATE()
UPDATE #TEMP2 SET COL5= dbo.fn_TidySpace_JCB_CLR(COL2)
FROM #TEMP2
PRINT 'CLR: using JCBnew space replacer C#'
PRINT DATEDIFF(ms,@TheTime,GETDATE())
go
DECLARE @TheTime DATETIME
SET @TheTime=GETDATE()
UPDATE #TEMP2 SET COL5= dbo.fn_TidySpace_PW_CLR(COL2)
FROM #TEMP2
PRINT 'CLR: using Paul White space replacer C#'
PRINT DATEDIFF(ms,@TheTime,GETDATE())
go
DECLARE @TheTime DATETIME
SET @TheTime=GETDATE()
UPDATE #TEMP2 SET COL5= dbo.fn_TidySpace_FLO_CLR(COL2)
FROM #TEMP2
PRINT 'CLR: using Flo s space replacer C#'
PRINT DATEDIFF(ms,@TheTime,GETDATE())
go
--CLEANUP
DROP FUNCTION dbo.fn_CleanUp
DROP TABLE #TEMP1
DROP TABLE #TEMP2
GO
--SET STATISTICS TIME OFF
C# Gnu
____________________________________________________
Excel Database Tasks : efficient business data processing
Version 3.3 released February 2015 -
Paul White (11/18/2009)
To the C# crew:This seems to be the fastest method so far (on my machine at least)...would one or more of you please try it out?
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
public partial class UserDefinedFunctions
{
/// <summary>
/// The general plan here is to scan the input string left to right,
/// writing the de-double-spaced result back into the same buffer
/// as we go.
///
/// While scanning, when we come across a space, we arrange to
/// skip following spaces. Skipping forward means advancing the
/// read position while not moving the write position pointer.
///
/// Since the output string will always be less than or equal in
/// length to the input string, this is safe to do.
///
/// The idea is that manipulating the buffer directly (but still in safe
/// managed code!) might be faster than the built-in string methods.
///
/// The char[] buffer internal to a SqlChars object is mutable -
/// unlike strings which are destroyed and re-created when changed.
///
/// </summary>
/// <param name="Input">The string to process, as SqlChars</param>
/// <returns>The processed result, as a SqlString</returns>
[SqlFunction
(
DataAccess = DataAccessKind.None,
SystemDataAccess = SystemDataAccessKind.None,
IsDeterministic = true,
IsPrecise = true
)
]
[return: SqlFacet(MaxSize = 4000)]
public static SqlString SpaceReplacer
(
[SqlFacet(IsNullable = false, MaxSize = 4000)] SqlChars Input
)
{
const char SPACE = ' ';
bool skipping = false; // In space-skipping mode
char[] input = Input.Buffer; // Reference to the internal char[] buffer of the SqlChars input
int length = input.Length; // Remember the length of the original
int writePos = 0; // Initialise the writing position
// Loop through each character
// scan = current scan position
// write = current write position
for (int readPos = 0; readPos < length; readPos++)
{
// Skip spaces if we are in 'skip mode' (see later)
if (skipping)
{
// Space skipping
while (readPos < length && input[readPos] == SPACE) { readPos++; }
// Bail if we ran out of string
if (readPos >= length) { break; }
}
// If the write position is before the scan position,
// the output string is no longer in sync with the input
// (we must have skipped spaces at some point).
// So, we need to copy the current character
// from the read position to the write position
if (writePos < readPos) { input[writePos] = input[readPos]; }
// Advance the write position
// (for all non-spaces, and for the first space in a sequence of spaces)
writePos++;
// Set or reset skip mode
skipping = (input[readPos] == SPACE);
}
// Return a string constructed from the part of
// the buffer we actually wrote to
return new SqlString(new string(input, 0, writePos));
}
};
Just out of interest I converted your code to use pointers which improved performance by 5-10%.
Not that I would recommend using UNSAFE code though...
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
Earlier on in this post I mentioned the use of a binary approach. I finally found some time to try this out.
declare @s-2 varchar(8000)
set @s-2 = '*' + replicate(' ',7998) + '*'
select len(@s)
set @s-2 = replace(replace(replace(replace(replace(replace(ltrim(rtrim(@s)),
replicate(' ',32+1),' '),replicate(' ',16+1),' '),replicate(' ',8+1),' '),replicate(' ',4+1),' '),replicate(' ',2+1),' '),replicate(' ',1+1),' ')
select len(@s)
select @s-2
Needless to say I have tried this with Jeff's data as well as with some random data and it works just fine.
As for performance, I generated 100000 records and Jeff's code did in 110 seconds whereas my code did in 16 seconds.
I shaved off a second by replacing the calls to replicate (which I used for clarity) with hardcoded constants or variables.
Oh yes, Jeff's code ran at 100% CPU whereas my code never reached 100%.
There is a recursive effect going on which I'm still trying to understand from a methematical point of view, i.e. for a given N+1 spaces what is the maximum length of blanks that can be reduce to 1 blank using the above approach.
Of course, if someone can find a string with blanks where this doesn't work I will quietly shut up.
-
Hi Old Hand
I hope you don't mind I wrapped you up in function
CREATE FUNCTION dbo.fn_CleanUp_OldHand(@S VARCHAR(8000))RETURNS VARCHAR(8000)
BEGIN
--declare @s-2 varchar(8000)
set @s-2 = '*' + replicate(' ',7998) + '*'
--select len(@s)
set @s-2 = replace(replace(replace(replace(replace(replace(ltrim(rtrim(@s)),
replicate(' ',32+1),' '),replicate(' ',16+1),' '),replicate(' ',8+1),' '),replicate(' ',4+1),' '),replicate(' ',2+1),' '),replicate(' ',1+1),' ')
--select len(@s)
RETURN @s-2
END
And here you are :
Beginning execution loop
Batch execution completed 10000 times.
SQL: using ||*9*9||
6570
SQL: Jeffs single char
3053
SQL:Looping
2696
SQL:Old Hand
1720
CLR: looping
453
CLR: Not looping using ||*9*9|| technique in C#
293
CLR: Not looping using Jeff's single char technique in C#
253
CLR: using JCBnew space replacer C#
163
CLR: using Paul White space replacer C#
110
CLR: using Flo s space replacer C#
110
C# Gnu
____________________________________________________
Excel Database Tasks : efficient business data processing
Version 3.3 released February 2015 -
Old Hand
no that set statement should be ommitted - now see where u are 🙂
Beginning execution loop
Batch execution completed 10000 times.
SQL: using ||*9*9||
6343
SQL: Jeffs single char
2896
SQL:Looping
2750
CLR: looping
446
CLR: Not looping using ||*9*9|| technique in C#
370
SQL:Old Hand
326
CLR: Not looping using Jeff's single char technique in C#
246
CLR: using JCBnew space replacer C#
163
CLR: using Paul White space replacer C#
110
CLR: using Flo s space replacer C#
110
Is this right Old Hand?
CREATE FUNCTION dbo.fn_CleanUp_OldHand(@S VARCHAR(8000))RETURNS VARCHAR(8000)
BEGIN
--declare @s-2 varchar(8000)
--set @s-2 = '*' + replicate(' ',7998) + '*'
--select len(@s)
set @s-2 = replace(replace(replace(replace(replace(replace(ltrim(rtrim(@s)),
replicate(' ',32+1),' '),replicate(' ',16+1),' '),replicate(' ',8+1),' '),replicate(' ',4+1),' '),replicate(' ',2+1),' '),replicate(' ',1+1),' ')
--select len(@s)
RETURN @s-2
END
C# Gnu
____________________________________________________
Excel Database Tasks : efficient business data processing
Version 3.3 released February 2015 -
C# Screw, try to call him "Michael" or "Michael Meierruth". "Old Hand" is his state depending on the count of posts here - like "SSC Rookie" in your case. 😉
-
Florian Reischl (11/18/2009)
C# Screw, try to call him "Michael" or "Michael Meierruth". "Old Hand" is his state depending on the count of posts here - like "SSC Rookie" in your case. 😉oops am very very sorry Michael - was just off my browser
I am just placing Jeff's and other submitted SQL into functions just to be fair to all...
C# Gnu
____________________________________________________
Excel Database Tasks : efficient business data processing
Version 3.3 released February 2015 -
Hello C# Screw,
I'm surprised to see me below a CLR implementation.
You might want to put in this line which uses hard coded string constants instead of the replicate function.
I might just get below that second CLR implementation.
set @s-2 = replace(replace(replace(replace(replace(replace(ltrim(rtrim(@s)),
' ',' '),
' ',' '),
' ',' '),
' ',' '),
' ',' '),
' ',' ')
Viewing 15 posts - 106 through 120 (of 425 total)
You must be logged in to reply to this topic. Login to reply