Intresting Query
-
Strange. I was getting quite inconsistent results.
Curious how my terse version compares to yours on your machine in terms of consistency (the test harness doesn't use that).
The point though about using @Holder as VARCHAR(20) was that your version returns an INT, so it doesn't need to CAST to @Holder when it is INT, whereas the REPLACE version does (because it returns a VARCHAR).
Must be careful to compare apples to apples.
My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St -
Eugene Elutin (8/10/2012)
You should try CLR too...Hah! I was waiting to hear someone suggest that. Not surprised it was you.
Where have you been lately Eugene? Haven't seen you in the T-SQL forums much in recent days.
My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St -
dwain.c (8/10/2012)
Eugene Elutin (8/10/2012)
You should try CLR too...Hah! I was waiting to hear someone suggest that. Not surprised it was you.
Where have you been lately Eugene? Haven't seen you in the T-SQL forums much in recent days.
Work + Holiday + Work...
😀
Actually, I guess Regex.Replace will outperform SQL on strings with a lot of leading and trailing zeros...
-
Eugene Elutin (8/10/2012)
dwain.c (8/10/2012)
Eugene Elutin (8/10/2012)
You should try CLR too...Hah! I was waiting to hear someone suggest that. Not surprised it was you.
Where have you been lately Eugene? Haven't seen you in the T-SQL forums much in recent days.
Actually, I guess Regex.Replace will outperform SQL on strings with a lot of leading and trailing zeros...
Possibly so, although I can confirm that PATINDEX does not.
My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St -
...
Possibly so, although I can confirm that PATINDEX does not.
You cannot even remotely compare performance of PATINDEX and .NET Regex...
PATINDEX can only find the pattern in the string, but to replace you will need to use SQL REPLACE.
Regex has own Replace method which does perform many many times better than SQL one...
Actually, it doesn't take much effort to write own c# replace method based on byte-arrays which will outperform SQL one... Don't ask me to get you one, I think you can dig it up on inet.
-
SQLCLR doesn't perform that well
SET NOCOUNT ON;IF object_id('tempdb..#sampleData') IS NOT NULL
BEGIN
DROP TABLE #sampleData;
END;
--1,000,000 Random rows of data
SELECT TOP 1000000 IDENTITY(INT,1,1) AS [Sno],
'0000' + CAST((ABS(CHECKSUM(NEWID())) % 20000) + 1 AS VARCHAR(20)) + '0' AS [Column]
INTO #sampleData
FROM master.dbo.syscolumns sc1, master.dbo.syscolumns sc2, master.dbo.syscolumns sc3;
DECLARE @HOLDER VARCHAR(20);
PRINT REPLICATE('=',80);
PRINT 'CADAVRE';
PRINT REPLICATE('=',80);
SET STATISTICS TIME ON;
SELECT @HOLDER = CAST(REVERSE(CAST(REVERSE([Column]) AS INT)) AS INT)
FROM #sampleData;
SET STATISTICS TIME OFF;
PRINT REPLICATE('=',80);
PRINT 'MARK';
PRINT REPLICATE('=',80);
SET STATISTICS TIME ON;
SELECT @HOLDER = REPLACE(LTRIM(RTRIM(REPLACE([Column],'0',' '))),' ','0')
FROM #sampleData;
SET STATISTICS TIME OFF;
PRINT REPLICATE('=',80);
PRINT 'MARK''S IMPROVED';
PRINT REPLICATE('=',80);
SET STATISTICS TIME ON;
SELECT @HOLDER = REPLACE(LTRIM(RTRIM(REPLACE([Column] COLLATE Latin1_General_BIN2,'0',' '))),' ','0')
FROM #sampleData;
SET STATISTICS TIME OFF;
PRINT REPLICATE('=',80);
PRINT 'DWAIN';
PRINT REPLICATE('=',80);
SET STATISTICS TIME ON;
SELECT @HOLDER = REVERSE(1*REVERSE([Column] * 1))
FROM #sampleData
;
SET STATISTICS TIME OFF;
PRINT REPLICATE('=',80);
PRINT 'MARK''S SQLCLR';
PRINT REPLICATE('=',80);
SET STATISTICS TIME ON;
SELECT @HOLDER = dbo.StripZeros([Column])
FROM #sampleData;
SET STATISTICS TIME OFF;
================================================================================CADAVRE
================================================================================
SQL Server Execution Times:
CPU time = 999 ms, elapsed time = 998 ms.
================================================================================
MARK
================================================================================
SQL Server Execution Times:
CPU time = 5023 ms, elapsed time = 5054 ms.
================================================================================
MARK'S IMPROVED
================================================================================
SQL Server Execution Times:
CPU time = 1170 ms, elapsed time = 1170 ms.
================================================================================
DWAIN
================================================================================
SQL Server Execution Times:
CPU time = 967 ms, elapsed time = 967 ms.
================================================================================
MARK'S SQLCLR
================================================================================
SQL Server Execution Times:
CPU time = 2262 ms, elapsed time = 2277 ms.
using System;using System.Data.SqlTypes;
using System.Text;
public partial class UserDefinedFunctions
{
[Microsoft.SqlServer.Server.SqlFunction]
public static SqlString StripZeros(SqlString s)
{
return (s.IsNull) ? SqlString.Null : new SqlString(s.Value.TrimStart('0').TrimEnd('0'));
}
};
____________________________________________________
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
-
Gosh! If you're gonna use the VARCHAR(20) version of @Holder, you should at least use this version of DWAIN's:
REVERSE(1*REVERSE([Column] * 1))
My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St -
CLR Version 1:
using System;using System.Data.SqlTypes;
namespace TrimTrailingLeading
{
public class UserDefinedFunctions
{
[Microsoft.SqlServer.Server.SqlFunction]
public static SqlString Trim(SqlString inputString)
{
string returnString;
try
{
returnString = inputString.ToString().TrimStart('0').TrimEnd('0');
}
catch (Exception)
{
returnString = "Error";
}
return new SqlString(returnString);
}
};
}
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Trim]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))BEGIN
DROP FUNCTION [dbo].[Trim];
END
IF EXISTS (SELECT * FROM sys.assemblies asms WHERE asms.name = N'TrimTrailingLeading' and is_user_defined = 1)
BEGIN
DROP ASSEMBLY [TrimTrailingLeading];
END
CREATE ASSEMBLY [TrimTrailingLeading]
AUTHORIZATION [dbo]
FROM 0x4D5A90000300000004000000FFFF0000B800000000000000400000000000000000000000000000000000000000000000000000000000000000000000800000000E1FBA0E00B409CD21B8014CCD21546869732070726F6772616D2063616E6E6F742062652072756E20696E20444F53206D6F64652E0D0D0A2400000000000000504500004C0103000CE124500000000000000000E00002210B010800000800000006000000000000CE2700000020000000400000000040000020000000020000040000000000000004000000000000000080000000020000000000000300408500001000001000000000100000100000000000001000000000000000000000007827000053000000004000008803000000000000000000000000000000000000006000000C000000C42600001C0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000200000080000000000000000000000082000004800000000000000000000002E74657874000000D4070000002000000008000000020000000000000000000000000000200000602E72737263000000880300000040000000040000000A0000000000000000000000000000400000402E72656C6F6300000C0000000060000000020000000E00000000000000000000000000004000004200000000000000000000000000000000B0270000000000004800000002000500C42000000006000001000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001B3004004E0000000100001100000F00FE16020000016F0F00000A178D120000010C08161F309D086F1000000A178D120000010C08161F309D086F1100000A0A00DE0B260072010000700A00DE000006731200000A0B2B00072A000001100000000001003637000B140000011E02281300000A2A42534A4201000100000000000C00000076322E302E35303732370000000005006C000000FC010000237E000068020000CC02000023537472696E6773000000003405000010000000235553004405000010000000234755494400000054050000AC00000023426C6F620000000000000002000001471502000900000000FA2533001600000100000014000000020000000200000001000000130000000C00000001000000010000000200000000000A0001000000000006005B0054000A0083006E000600B600A4000600CD00A4000600EA00A40006000901A40006002201A40006003B01A40006005601A40006007101A4000600A9018A010600BD01A4000600E901D6013700FD01000006002C020C0206004C020C020A0085026A020600A30254000600A80254000600C10254000000000001000000000001000100010010002200370005000100010050200000000096008D000A000100BC20000000008618920011000200000001009800190092001500210092001500290092001500310092001500390092001500410092001500490092001500510092001500590092001A00610092001500690092001F0079009200250081009200110089009200110009009A022F009900AF0233009900B9023300110092001500090092001100200073002A002E002B0041002E0013005A002E001B005A002E0023005A002E000B0041002E00330060002E003B005A002E004B005A002E005B0078002E00630081002E006B008A0039000480000001000000FD1106490000000000003700000002000000000000000000000001004B000000000002000000000000000000000001006200000000000000003C4D6F64756C653E005472696D547261696C696E674C656164696E672E646C6C0055736572446566696E656446756E6374696F6E73005472696D547261696C696E674C656164696E67006D73636F726C69620053797374656D004F626A6563740053797374656D2E446174610053797374656D2E446174612E53716C54797065730053716C537472696E67005472696D002E63746F7200696E707574537472696E670053797374656D2E5265666C656374696F6E00417373656D626C795469746C6541747472696275746500417373656D626C794465736372697074696F6E41747472696275746500417373656D626C79436F6E66696775726174696F6E41747472696275746500417373656D626C79436F6D70616E7941747472696275746500417373656D626C7950726F6475637441747472696275746500417373656D626C79436F7079726967687441747472696275746500417373656D626C7954726164656D61726B41747472696275746500417373656D626C7943756C747572654174747269627574650053797374656D2E52756E74696D652E496E7465726F70536572766963657300436F6D56697369626C6541747472696275746500417373656D626C7956657273696F6E4174747269627574650053797374656D2E446961676E6F73746963730044656275676761626C6541747472696275746500446562756767696E674D6F6465730053797374656D2E52756E74696D652E436F6D70696C6572536572766963657300436F6D70696C6174696F6E52656C61786174696F6E734174747269627574650052756E74696D65436F6D7061746962696C697479417474726962757465004D6963726F736F66742E53716C5365727665722E5365727665720053716C46756E6374696F6E41747472696275746500546F537472696E67004368617200537472696E67005472696D5374617274005472696D456E6400457863657074696F6E0000000B4500720072006F00720000000000C9D8E211081FB649AEE67FE5993BDCC80008B77A5C561934E0890600011109110903200001042001010E0420010102052001011139042001010804010000000320000E0520010E1D030707030E11091D03180100135472696D547261696C696E674C656164696E67000005010000000017010012436F7079726967687420C2A920203230313200000801000701000000000801000800000000001E01000100540216577261704E6F6E457863657074696F6E5468726F777301000000000000000CE12450000000000200000098000000E0260000E00800005253445319EFCE3EB3B5BD46990E97B75770543D04000000633A5C75736572735C6372616967775C646F63756D656E74735C76697375616C2073747564696F20323031305C50726F6A656374735C5472696D547261696C696E674C656164696E675C5472696D547261696C696E674C656164696E675C6F626A5C44656275675C5472696D547261696C696E674C656164696E672E70646200A02700000000000000000000BE270000002000000000000000000000000000000000000000000000B027000000000000000000000000000000005F436F72446C6C4D61696E006D73636F7265652E646C6C0000000000FF2500204000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000100100000001800008000000000000000000000000000000100010000003000008000000000000000000000000000000100000000004800000058400000300300000000000000000000300334000000560053005F00560045005200530049004F004E005F0049004E0046004F0000000000BD04EFFE00000100000001000649FD11000001000649FD113F000000000000000400000002000000000000000000000000000000440000000100560061007200460069006C00650049006E0066006F00000000002400040000005400720061006E0073006C006100740069006F006E00000000000000B00490020000010053007400720069006E006700460069006C00650049006E0066006F0000006C0200000100300030003000300030003400620030000000500014000100460069006C0065004400650073006300720069007000740069006F006E00000000005400720069006D0054007200610069006C0069006E0067004C0065006100640069006E006700000040000F000100460069006C006500560065007200730069006F006E000000000031002E0030002E0034003600300035002E00310038003600390034000000000050001800010049006E007400650072006E0061006C004E0061006D00650000005400720069006D0054007200610069006C0069006E0067004C0065006100640069006E0067002E0064006C006C0000004800120001004C006500670061006C0043006F007000790072006900670068007400000043006F0070007900720069006700680074002000A90020002000320030003100320000005800180001004F0072006900670069006E0061006C00460069006C0065006E0061006D00650000005400720069006D0054007200610069006C0069006E0067004C0065006100640069006E0067002E0064006C006C000000480014000100500072006F0064007500630074004E0061006D006500000000005400720069006D0054007200610069006C0069006E0067004C0065006100640069006E006700000044000F000100500072006F006400750063007400560065007200730069006F006E00000031002E0030002E0034003600300035002E00310038003600390034000000000048000F00010041007300730065006D0062006C0079002000560065007200730069006F006E00000031002E0030002E0034003600300035002E003100380036003900340000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000002000000C000000D03700000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
WITH PERMISSION_SET = SAFE;
GO
CREATE FUNCTION [dbo].[Trim](@inputString [nvarchar](4000))
RETURNS [nvarchar](4000) WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [TrimTrailingLeading].[TrimTrailingLeading.UserDefinedFunctions].[Trim];
GO
SET NOCOUNT ON;IF object_id('tempdb..#sampleData') IS NOT NULL
BEGIN
DROP TABLE #sampleData;
END;
--1,000,000 Random rows of data
SELECT TOP 1000000 IDENTITY(INT,1,1) AS [Sno],
'0000' + CAST((ABS(CHECKSUM(NEWID())) % 20000) + 1 AS VARCHAR(20)) + '0' AS [Column]
INTO #sampleData
FROM master.dbo.syscolumns sc1, master.dbo.syscolumns sc2, master.dbo.syscolumns sc3;
DECLARE @HOLDER VARCHAR(20);
PRINT REPLICATE('=',80);
PRINT 'CADAVRE';
PRINT REPLICATE('=',80);
SET STATISTICS TIME ON;
SELECT @HOLDER = CAST(REVERSE(CAST(REVERSE([Column]) AS INT)) AS INT)
FROM #sampleData;
SET STATISTICS TIME OFF;
PRINT REPLICATE('=',80);
PRINT 'MARK';
PRINT REPLICATE('=',80);
SET STATISTICS TIME ON;
SELECT @HOLDER = REPLACE(LTRIM(RTRIM(REPLACE([Column],'0',' '))),' ','0')
FROM #sampleData;
SET STATISTICS TIME OFF;
PRINT REPLICATE('=',80);
PRINT 'MARK''S IMPROVED';
PRINT REPLICATE('=',80);
SET STATISTICS TIME ON;
SELECT @HOLDER = REPLACE(LTRIM(RTRIM(REPLACE([Column] COLLATE Latin1_General_BIN2,'0',' '))),' ','0')
FROM #sampleData;
SET STATISTICS TIME OFF;
PRINT REPLICATE('=',80);
PRINT 'DWAIN';
PRINT REPLICATE('=',80);
SET STATISTICS TIME ON;
SELECT @HOLDER = 1*REPLACE(RTRIM(REPLACE([Column] COLLATE Latin1_General_BIN2, '0', ' ')), ' ', '0')
FROM #sampleData
;
SET STATISTICS TIME OFF;
PRINT REPLICATE('=',80);
PRINT 'CLR 1';
PRINT REPLICATE('=',80);
SET STATISTICS TIME ON;
SELECT @HOLDER = [dbo].[Trim]([Column])
FROM #sampleData
SET STATISTICS TIME OFF;
================================================================================CADAVRE
================================================================================
SQL Server Execution Times:
CPU time = 750 ms, elapsed time = 755 ms.
================================================================================
MARK
================================================================================
SQL Server Execution Times:
CPU time = 1109 ms, elapsed time = 1098 ms.
================================================================================
MARK'S IMPROVED
================================================================================
SQL Server Execution Times:
CPU time = 875 ms, elapsed time = 882 ms.
================================================================================
DWAIN
================================================================================
SQL Server Execution Times:
CPU time = 1360 ms, elapsed time = 1368 ms.
================================================================================
CLR 1
================================================================================
SQL Server Execution Times:
CPU time = 2109 ms, elapsed time = 2402 ms.
OK, trim start and trim end are not so good 🙂
Regular expressions are not really my thing. If one of you can write a better one than this (I'm sure it can be done with 1 pattern, just don't know how to write it) then I'll try it again.
using System;using System.Data.SqlTypes;
using System.Text.RegularExpressions;
namespace TrimTrailingLeading
{
public class UserDefinedFunctions
{
[Microsoft.SqlServer.Server.SqlFunction]
public static SqlString Trim(SqlString inputString)
{
string returnString;
try
{
Regex leading = new Regex("^0+");
Regex trailing = new Regex("[0]+$");
returnString = trailing.Replace(leading.Replace(inputString.ToString(), string.Empty), string.Empty);
}
catch (Exception)
{
returnString = "Error";
}
return new SqlString(returnString);
}
};
}
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Trim]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))BEGIN
DROP FUNCTION [dbo].[Trim];
END
IF EXISTS (SELECT * FROM sys.assemblies asms WHERE asms.name = N'TrimTrailingLeading' and is_user_defined = 1)
BEGIN
DROP ASSEMBLY [TrimTrailingLeading];
END
CREATE ASSEMBLY [TrimTrailingLeading]
AUTHORIZATION [dbo]
FROM 0x4D5A90000300000004000000FFFF0000B800000000000000400000000000000000000000000000000000000000000000000000000000000000000000800000000E1FBA0E00B409CD21B8014CCD21546869732070726F6772616D2063616E6E6F742062652072756E20696E20444F53206D6F64652E0D0D0A2400000000000000504500004C01030083E424500000000000000000E00002210B010800000A000000060000000000001E280000002000000040000000004000002000000002000004000000000000000400000000000000008000000002000000000000030040850000100000100000000010000010000000000000100000000000000000000000D02700004B000000004000008803000000000000000000000000000000000000006000000C0000001C2700001C0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000200000080000000000000000000000082000004800000000000000000000002E746578740000002408000000200000000A000000020000000000000000000000000000200000602E72737263000000880300000040000000040000000C0000000000000000000000000000400000402E72656C6F6300000C000000006000000002000000100000000000000000000000000000400000420000000000000000000000000000000000280000000000004800000002000500CC2000005006000001000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001B300400560000000100001100007201000070730F00000A0B7209000070730F00000A0C08070F00FE16020000016F1000000A7E1100000A6F1200000A7E1100000A6F1200000A0A00DE0B260072150000700A00DE000006731300000A0D2B00092A000001100000000001003E3F000B140000011E02281400000A2A42534A4201000100000000000C00000076322E302E35303732370000000005006C00000018020000237E000084020000E802000023537472696E6773000000006C050000240000002355530090050000100000002347554944000000A0050000B000000023426C6F620000000000000002000001471502000900000000FA2533001600000100000014000000020000000200000001000000140000000C00000001000000010000000300000000000A0001000000000006005B0054000A0083006E000600B600A4000600CD00A4000600EA00A40006000901A40006002201A40006003B01A40006005601A40006007101A4000600A9018A010600BD01A4000600E901D6013700FD01000006002C020C0206004C020C020A0085026A020E00B9029A020600C80254000600DD0254000000000001000000000001000100010010002200370005000100010050200000000096008D000A000100C420000000008618920011000200000001009800190092001500210092001500290092001500310092001500390092001500410092001500490092001500510092001500590092001A00610092001500690092001F007900920025008100920011008900920011009100920015000900BF022F009900CF0233009100D5023600110092001500090092001100200073002A002E002B0046002E0013005F002E001B005F002E0023005F002E000B0046002E00330065002E003B005F002E004B005F002E005B007D002E00630086002E006B008F003C000480000001000000FD11C14A0000000000003700000002000000000000000000000001004B00000000000200000000000000000000000100620000000000020000000000000000000000010054000000000000000000003C4D6F64756C653E005472696D547261696C696E674C656164696E672E646C6C0055736572446566696E656446756E6374696F6E73005472696D547261696C696E674C656164696E67006D73636F726C69620053797374656D004F626A6563740053797374656D2E446174610053797374656D2E446174612E53716C54797065730053716C537472696E67005472696D002E63746F7200696E707574537472696E670053797374656D2E5265666C656374696F6E00417373656D626C795469746C6541747472696275746500417373656D626C794465736372697074696F6E41747472696275746500417373656D626C79436F6E66696775726174696F6E41747472696275746500417373656D626C79436F6D70616E7941747472696275746500417373656D626C7950726F6475637441747472696275746500417373656D626C79436F7079726967687441747472696275746500417373656D626C7954726164656D61726B41747472696275746500417373656D626C7943756C747572654174747269627574650053797374656D2E52756E74696D652E496E7465726F70536572766963657300436F6D56697369626C6541747472696275746500417373656D626C7956657273696F6E4174747269627574650053797374656D2E446961676E6F73746963730044656275676761626C6541747472696275746500446562756767696E674D6F6465730053797374656D2E52756E74696D652E436F6D70696C6572536572766963657300436F6D70696C6174696F6E52656C61786174696F6E734174747269627574650052756E74696D65436F6D7061746962696C697479417474726962757465004D6963726F736F66742E53716C5365727665722E5365727665720053716C46756E6374696F6E4174747269627574650053797374656D2E546578742E526567756C617245787072657373696F6E7300526567657800546F537472696E6700537472696E6700456D707479005265706C61636500457863657074696F6E000000075E0030002B00000B5B0030005D002B002400000B4500720072006F007200000000002661DE64237F164AB97E071FB03E0BB60008B77A5C561934E0890600011109110903200001042001010E0420010102052001011139042001010804010000000320000E02060E0520020E0E0E0907040E124912491109180100135472696D547261696C696E674C656164696E67000005010000000017010012436F7079726967687420C2A920203230313200000801000701000000000801000800000000001E01000100540216577261704E6F6E457863657074696F6E5468726F77730100000000000083E4245000000000020000009800000038270000380900005253445319EFCE3EB3B5BD46990E97B75770543D05000000633A5C75736572735C6372616967775C646F63756D656E74735C76697375616C2073747564696F20323031305C50726F6A656374735C5472696D547261696C696E674C656164696E675C5472696D547261696C696E674C656164696E675C6F626A5C44656275675C5472696D547261696C696E674C656164696E672E70646200F827000000000000000000000E280000002000000000000000000000000000000000000000000000002800000000000000005F436F72446C6C4D61696E006D73636F7265652E646C6C0000000000FF2500204000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000100100000001800008000000000000000000000000000000100010000003000008000000000000000000000000000000100000000004800000058400000300300000000000000000000300334000000560053005F00560045005200530049004F004E005F0049004E0046004F0000000000BD04EFFE0000010000000100C14AFD1100000100C14AFD113F000000000000000400000002000000000000000000000000000000440000000100560061007200460069006C00650049006E0066006F00000000002400040000005400720061006E0073006C006100740069006F006E00000000000000B00490020000010053007400720069006E006700460069006C00650049006E0066006F0000006C0200000100300030003000300030003400620030000000500014000100460069006C0065004400650073006300720069007000740069006F006E00000000005400720069006D0054007200610069006C0069006E0067004C0065006100640069006E006700000040000F000100460069006C006500560065007200730069006F006E000000000031002E0030002E0034003600300035002E00310039003100330037000000000050001800010049006E007400650072006E0061006C004E0061006D00650000005400720069006D0054007200610069006C0069006E0067004C0065006100640069006E0067002E0064006C006C0000004800120001004C006500670061006C0043006F007000790072006900670068007400000043006F0070007900720069006700680074002000A90020002000320030003100320000005800180001004F0072006900670069006E0061006C00460069006C0065006E0061006D00650000005400720069006D0054007200610069006C0069006E0067004C0065006100640069006E0067002E0064006C006C000000480014000100500072006F0064007500630074004E0061006D006500000000005400720069006D0054007200610069006C0069006E0067004C0065006100640069006E006700000044000F000100500072006F006400750063007400560065007200730069006F006E00000031002E0030002E0034003600300035002E00310039003100330037000000000048000F00010041007300730065006D0062006C0079002000560065007200730069006F006E00000031002E0030002E0034003600300035002E003100390031003300370000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000002000000C000000203800000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
WITH PERMISSION_SET = SAFE;
GO
CREATE FUNCTION [dbo].[Trim](@inputString [nvarchar](4000))
RETURNS [nvarchar](4000) WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [TrimTrailingLeading].[TrimTrailingLeading.UserDefinedFunctions].[Trim];
SET NOCOUNT ON;IF object_id('tempdb..#sampleData') IS NOT NULL
BEGIN
DROP TABLE #sampleData;
END;
--1,000,000 Random rows of data
SELECT TOP 1000000 IDENTITY(INT,1,1) AS [Sno],
'0000' + CAST((ABS(CHECKSUM(NEWID())) % 20000) + 1 AS VARCHAR(20)) + '0' AS [Column]
INTO #sampleData
FROM master.dbo.syscolumns sc1, master.dbo.syscolumns sc2, master.dbo.syscolumns sc3;
DECLARE @HOLDER VARCHAR(20);
PRINT REPLICATE('=',80);
PRINT 'CADAVRE';
PRINT REPLICATE('=',80);
SET STATISTICS TIME ON;
SELECT @HOLDER = CAST(REVERSE(CAST(REVERSE([Column]) AS INT)) AS INT)
FROM #sampleData;
SET STATISTICS TIME OFF;
PRINT REPLICATE('=',80);
PRINT 'MARK';
PRINT REPLICATE('=',80);
SET STATISTICS TIME ON;
SELECT @HOLDER = REPLACE(LTRIM(RTRIM(REPLACE([Column],'0',' '))),' ','0')
FROM #sampleData;
SET STATISTICS TIME OFF;
PRINT REPLICATE('=',80);
PRINT 'MARK''S IMPROVED';
PRINT REPLICATE('=',80);
SET STATISTICS TIME ON;
SELECT @HOLDER = REPLACE(LTRIM(RTRIM(REPLACE([Column] COLLATE Latin1_General_BIN2,'0',' '))),' ','0')
FROM #sampleData;
SET STATISTICS TIME OFF;
PRINT REPLICATE('=',80);
PRINT 'DWAIN';
PRINT REPLICATE('=',80);
SET STATISTICS TIME ON;
SELECT @HOLDER = 1*REPLACE(RTRIM(REPLACE([Column] COLLATE Latin1_General_BIN2, '0', ' ')), ' ', '0')
FROM #sampleData
;
SET STATISTICS TIME OFF;
PRINT REPLICATE('=',80);
PRINT 'CLR 2';
PRINT REPLICATE('=',80);
SET STATISTICS TIME ON;
SELECT @HOLDER = [dbo].[Trim]([Column])
FROM #sampleData
SET STATISTICS TIME OFF;
================================================================================CADAVRE
================================================================================
SQL Server Execution Times:
CPU time = 766 ms, elapsed time = 760 ms.
================================================================================
MARK
================================================================================
SQL Server Execution Times:
CPU time = 1109 ms, elapsed time = 1110 ms.
================================================================================
MARK'S IMPROVED
================================================================================
SQL Server Execution Times:
CPU time = 922 ms, elapsed time = 915 ms.
================================================================================
DWAIN
================================================================================
SQL Server Execution Times:
CPU time = 1328 ms, elapsed time = 1332 ms.
================================================================================
CLR 2
================================================================================
SQL Server Execution Times:
CPU time = 29829 ms, elapsed time = 31211 ms.
Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/ -
dwain.c (8/10/2012)
Gosh! If you're gonna use the VARCHAR(20) version of @Holder, you should at least use this version of DWAIN's:REVERSE(1*REVERSE([Column] * 1))Oops, my bad. Original post updated.
____________________________________________________
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 -
try to return
(SqlString) s.Value.TrimStart('0').TrimEnd('0');
instead of
new SqlString(s.Value.TrimStart('0').TrimEnd('0'));
what about Regex?
Don't forget to create static Regex object instead of instance in the function.
To remove leading and trailing zeros: "[^0][0-9]*[^0]"
-
Eugene Elutin (8/10/2012)
try to return(SqlString) s.Value.TrimStart('0').TrimEnd('0');
instead of
new SqlString(s.Value.TrimStart('0').TrimEnd('0'));
Marginal improvement
From this
CPU time = 2262 ms, elapsed time = 2277 ms.to this
CPU time = 2169 ms, elapsed time = 2184 ms.____________________________________________________
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 -
Eugene Elutin (8/10/2012)
try to return(SqlString) s.Value.TrimStart('0').TrimEnd('0');
instead of
new SqlString(s.Value.TrimStart('0').TrimEnd('0'));
what about Regex?
Don't forget to create static Regex object instead of instance in the function.
To remove leading and trailing zeros: "[^0][0-9]*[^0]"
Changed my regex to use your single pattern instead of the two I was using: -
using System;using System.Data.SqlTypes;
using System.Text.RegularExpressions;
namespace TrimTrailingLeading
{
public class UserDefinedFunctions
{
[Microsoft.SqlServer.Server.SqlFunction]
public static SqlString Trim(SqlString inputString)
{
try
{
Regex expr = new Regex("[^0][0-9]*[^0]");
return expr.Replace(inputString.ToString(), string.Empty);
}
catch (Exception)
{
return "Error";
}
}
};
}
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Trim]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))BEGIN
DROP FUNCTION [dbo].[Trim];
END
IF EXISTS (SELECT * FROM sys.assemblies asms WHERE asms.name = N'TrimTrailingLeading' and is_user_defined = 1)
BEGIN
DROP ASSEMBLY [TrimTrailingLeading];
END
CREATE ASSEMBLY [TrimTrailingLeading]
AUTHORIZATION [dbo]
FROM 0x4D5A90000300000004000000FFFF0000B800000000000000400000000000000000000000000000000000000000000000000000000000000000000000800000000E1FBA0E00B409CD21B8014CCD21546869732070726F6772616D2063616E6E6F742062652072756E20696E20444F53206D6F64652E0D0D0A2400000000000000504500004C010300F2E624500000000000000000E00002210B010800000A000000060000000000001E280000002000000040000000004000002000000002000004000000000000000400000000000000008000000002000000000000030040850000100000100000000010000010000000000000100000000000000000000000D02700004B000000004000008803000000000000000000000000000000000000006000000C0000001C2700001C0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000200000080000000000000000000000082000004800000000000000000000002E746578740000002408000000200000000A000000020000000000000000000000000000200000602E72737263000000880300000040000000040000000C0000000000000000000000000000400000402E72656C6F6300000C000000006000000002000000100000000000000000000000000000400000420000000000000000000000000000000000280000000000004800000002000500B42000006806000001000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001B3003003F0000000100001100007201000070730F00000A0A060F00FE16020000016F1000000A7E1100000A6F1200000A281300000A0BDE0F2600721F000070281300000A0BDE0000072A0001100000000001002C2D000F140000011E02281400000A2A42534A4201000100000000000C00000076322E302E35303732370000000005006C00000018020000237E000084020000F402000023537472696E677300000000780500002C00000023555300A4050000100000002347554944000000B4050000B400000023426C6F620000000000000002000001471502000900000000FA2533001600000100000014000000020000000200000001000000140000000C00000001000000010000000300000000000A0001000000000006005B0054000A0083006E000600B600A4000600CD00A4000600EA00A40006000901A40006002201A40006003B01A40006005601A40006007101A4000600A9018A010600BD01A4000600E901D6013700FD01000006002C020C0206004C020C020A0085026A020E00B9029A020600C80254000600E90254000000000001000000000001000100010010002200370005000100010050200000000096008D000A000100AC20000000008618920011000200000001009800190092001500210092001500290092001500310092001500390092001500410092001500490092001500510092001500590092001A00610092001500690092001F007900920025008100920011008900920011009100920015000900BF022F009900CF0233009100D50236001100DD023C00090092001100200073002A002E002B0049002E00130062002E001B0062002E00230062002E000B0049002E00330068002E003B0062002E004B0062002E005B0080002E00630089002E006B00920042000480000001000000FD11F94B0000000000003700000002000000000000000000000001004B00000000000200000000000000000000000100620000000000020000000000000000000000010054000000000000000000003C4D6F64756C653E005472696D547261696C696E674C656164696E672E646C6C0055736572446566696E656446756E6374696F6E73005472696D547261696C696E674C656164696E67006D73636F726C69620053797374656D004F626A6563740053797374656D2E446174610053797374656D2E446174612E53716C54797065730053716C537472696E67005472696D002E63746F7200696E707574537472696E670053797374656D2E5265666C656374696F6E00417373656D626C795469746C6541747472696275746500417373656D626C794465736372697074696F6E41747472696275746500417373656D626C79436F6E66696775726174696F6E41747472696275746500417373656D626C79436F6D70616E7941747472696275746500417373656D626C7950726F6475637441747472696275746500417373656D626C79436F7079726967687441747472696275746500417373656D626C7954726164656D61726B41747472696275746500417373656D626C7943756C747572654174747269627574650053797374656D2E52756E74696D652E496E7465726F70536572766963657300436F6D56697369626C6541747472696275746500417373656D626C7956657273696F6E4174747269627574650053797374656D2E446961676E6F73746963730044656275676761626C6541747472696275746500446562756767696E674D6F6465730053797374656D2E52756E74696D652E436F6D70696C6572536572766963657300436F6D70696C6174696F6E52656C61786174696F6E734174747269627574650052756E74696D65436F6D7061746962696C697479417474726962757465004D6963726F736F66742E53716C5365727665722E5365727665720053716C46756E6374696F6E4174747269627574650053797374656D2E546578742E526567756C617245787072657373696F6E7300526567657800546F537472696E6700537472696E6700456D707479005265706C616365006F705F496D706C6963697400457863657074696F6E0000001D5B005E0030005D005B0030002D0039005D002A005B005E0030005D00010B4500720072006F00720000001131A5738988CA4B9F18E91DB1A88AE10008B77A5C561934E0890600011109110903200001042001010E0420010102052001011139042001010804010000000320000E02060E0520020E0E0E05000111090E06070212491109180100135472696D547261696C696E674C656164696E67000005010000000017010012436F7079726967687420C2A920203230313200000801000701000000000801000800000000001E01000100540216577261704E6F6E457863657074696F6E5468726F77730100000000000000F2E6245000000000020000009800000038270000380900005253445319EFCE3EB3B5BD46990E97B75770543D06000000633A5C75736572735C6372616967775C646F63756D656E74735C76697375616C2073747564696F20323031305C50726F6A656374735C5472696D547261696C696E674C656164696E675C5472696D547261696C696E674C656164696E675C6F626A5C44656275675C5472696D547261696C696E674C656164696E672E70646200F827000000000000000000000E280000002000000000000000000000000000000000000000000000002800000000000000005F436F72446C6C4D61696E006D73636F7265652E646C6C0000000000FF2500204000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000100100000001800008000000000000000000000000000000100010000003000008000000000000000000000000000000100000000004800000058400000300300000000000000000000300334000000560053005F00560045005200530049004F004E005F0049004E0046004F0000000000BD04EFFE0000010000000100F94BFD1100000100F94BFD113F000000000000000400000002000000000000000000000000000000440000000100560061007200460069006C00650049006E0066006F00000000002400040000005400720061006E0073006C006100740069006F006E00000000000000B00490020000010053007400720069006E006700460069006C00650049006E0066006F0000006C0200000100300030003000300030003400620030000000500014000100460069006C0065004400650073006300720069007000740069006F006E00000000005400720069006D0054007200610069006C0069006E0067004C0065006100640069006E006700000040000F000100460069006C006500560065007200730069006F006E000000000031002E0030002E0034003600300035002E00310039003400340039000000000050001800010049006E007400650072006E0061006C004E0061006D00650000005400720069006D0054007200610069006C0069006E0067004C0065006100640069006E0067002E0064006C006C0000004800120001004C006500670061006C0043006F007000790072006900670068007400000043006F0070007900720069006700680074002000A90020002000320030003100320000005800180001004F0072006900670069006E0061006C00460069006C0065006E0061006D00650000005400720069006D0054007200610069006C0069006E0067004C0065006100640069006E0067002E0064006C006C000000480014000100500072006F0064007500630074004E0061006D006500000000005400720069006D0054007200610069006C0069006E0067004C0065006100640069006E006700000044000F000100500072006F006400750063007400560065007200730069006F006E00000031002E0030002E0034003600300035002E00310039003400340039000000000048000F00010041007300730065006D0062006C0079002000560065007200730069006F006E00000031002E0030002E0034003600300035002E003100390034003400390000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000002000000C000000203800000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
WITH PERMISSION_SET = SAFE;
GO
CREATE FUNCTION [dbo].[Trim](@inputString [nvarchar](4000))
RETURNS [nvarchar](4000) WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [TrimTrailingLeading].[TrimTrailingLeading.UserDefinedFunctions].[Trim];
================================================================================CLR 3
================================================================================
SQL Server Execution Times:
CPU time = 21719 ms, elapsed time = 22787 ms.
Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/ -
I don't have VS to test...
try:
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Text.RegularExpressions;
public partial class UserDefinedFunctions
{
static readonly Regex _regex = new Regex(@"[^0][0-9]*[^0]", RegexOptions.Compiled);
[Microsoft.SqlServer.Server.SqlFunction]
public static SqlString RemoveLTZeros(SqlString val)
{
// Put your code here
if (val.IsNull) return SqlString.Null;
return (SqlString)_regex.Replace(val.ToString(), String.Empty);
}
};
Few things to note:
1. Use static Regex, so it doesn't need to be created for every function call
2. Regex expression compiled once when static regex is initialised
3. Return from function as soon as possible, shorter version of code (using ? in return) is not always the best performer...
4. Do not create new SqlString, just cast the result of regex.replace
AND NOT USE TRY TO CATCH THE ERROR!!!
-
Eugene Elutin (8/10/2012)
4. Do not create new SqlString, just cast the result of regex.replaceThat's redundant.
using System;using System.Data.SqlTypes;
using System.Text.RegularExpressions;
namespace TrimTrailingLeading
{
public class UserDefinedFunctions
{
private static readonly Regex Regex = new Regex(@"[^0][0-9]*[^0]", RegexOptions.Compiled);
[Microsoft.SqlServer.Server.SqlFunction]
public static SqlString Trim(SqlString inputString)
{
if (inputString.IsNull) return SqlString.Null;
return Regex.Replace(inputString.ToString(), String.Empty);
}
}
};
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Trim]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))BEGIN
DROP FUNCTION [dbo].[Trim]
END
IF EXISTS (SELECT * FROM sys.assemblies asms WHERE asms.name = N'TrimTrailingLeading' and is_user_defined = 1)
BEGIN
DROP ASSEMBLY [TrimTrailingLeading];
END
CREATE ASSEMBLY [TrimTrailingLeading]
AUTHORIZATION [dbo]
FROM 0x4D5A90000300000004000000FFFF0000B800000000000000400000000000000000000000000000000000000000000000000000000000000000000000800000000E1FBA0E00B409CD21B8014CCD21546869732070726F6772616D2063616E6E6F742062652072756E20696E20444F53206D6F64652E0D0D0A2400000000000000504500004C01030011E924500000000000000000E00002210B010800000A000000060000000000006E2800000020000000400000000040000020000000020000040000000000000004000000000000000080000000020000000000000300408500001000001000000000100000100000000000001000000000000000000000001828000053000000004000008803000000000000000000000000000000000000006000000C000000642700001C0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000200000080000000000000000000000082000004800000000000000000000002E746578740000007408000000200000000A000000020000000000000000000000000000200000602E72737263000000880300000040000000040000000C0000000000000000000000000000400000402E72656C6F6300000C000000006000000002000000100000000000000000000000000000400000420000000000000000000000000000000050280000000000004800000002000500B4200000B00600000100000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000133003003D00000001000011000F00280F00000A16FE010B072D087E1000000A0A2B247E010000040F00FE16030000016F1100000A7E1200000A6F1300000A281400000A0A2B00062A4672010000701E731500000A80010000042A1E02281600000A2A0042534A4201000100000000000C00000076322E302E35303732370000000005006C0000003C020000237E0000A80200001003000023537472696E677300000000B80500002000000023555300D8050000100000002347554944000000E8050000C800000023426C6F620000000000000002000001571502000900000000FA253300160000010000001400000002000000010000000300000001000000160000000C00000001000000010000000300000000000A0001000000000006005B0054000A00810062000E00A80093000600DB00C9000600F200C90006000F01C90006002E01C90006004701C90006006001C90006007B01C90006009601C9000600CE01AF010600E201C90006000E02FB013B00220200000600510231020600710231020E00AA028F020600D80254000A000003620000000000010000000000010001000100100022003700050001000100310081000A005020000000009600B2000E000100AB20000000008618B700150002009920000000009118F9025400020000000100BD002100B70019002900B70019003100B70019003900B70019004100B70019004900B70019005100B70019005900B70019006100B7001E006900B70019007100B70023008100B70029008900B70015009100B70015001900BF0233001900CA0237000900CF023B009900DF023F001100E50242001900ED0248001100B70058000900B7001500200073002E002E002B005F002E00130078002E001B0078002E00230078002E000B005F002E0033007E002E003B0078002E004B0078002E005B0096002E0063009F002E006B00A8004E000480000001000000FD11084D0000000000003700000002000000000000000000000001004B00000000000200000000000000000000000100540000000000020000000000000000000000010087000000000000000000003C4D6F64756C653E005472696D547261696C696E674C656164696E672E646C6C0055736572446566696E656446756E6374696F6E73005472696D547261696C696E674C656164696E67006D73636F726C69620053797374656D004F626A6563740053797374656D2E546578742E526567756C617245787072657373696F6E730052656765780053797374656D2E446174610053797374656D2E446174612E53716C54797065730053716C537472696E67005472696D002E63746F7200696E707574537472696E670053797374656D2E5265666C656374696F6E00417373656D626C795469746C6541747472696275746500417373656D626C794465736372697074696F6E41747472696275746500417373656D626C79436F6E66696775726174696F6E41747472696275746500417373656D626C79436F6D70616E7941747472696275746500417373656D626C7950726F6475637441747472696275746500417373656D626C79436F7079726967687441747472696275746500417373656D626C7954726164656D61726B41747472696275746500417373656D626C7943756C747572654174747269627574650053797374656D2E52756E74696D652E496E7465726F70536572766963657300436F6D56697369626C6541747472696275746500417373656D626C7956657273696F6E4174747269627574650053797374656D2E446961676E6F73746963730044656275676761626C6541747472696275746500446562756767696E674D6F6465730053797374656D2E52756E74696D652E436F6D70696C6572536572766963657300436F6D70696C6174696F6E52656C61786174696F6E734174747269627574650052756E74696D65436F6D7061746962696C697479417474726962757465004D6963726F736F66742E53716C5365727665722E5365727665720053716C46756E6374696F6E417474726962757465006765745F49734E756C6C004E756C6C00546F537472696E6700537472696E6700456D707479005265706C616365006F705F496D706C69636974002E6363746F720052656765784F7074696F6E7300000000001D5B005E0030005D005B0030002D0039005D002A005B005E0030005D000100BF3310CCFEE34543BB3E55B7F9FC8F6C0008B77A5C561934E08903061209060001110D110D03200001042001010E042001010205200101113D04200101080401000000032000020306110D0320000E02060E0520020E0E0E050001110D0E050702110D0203000001062002010E1151180100135472696D547261696C696E674C656164696E67000005010000000017010012436F7079726967687420C2A920203230313200000801000701000000000801000800000000001E01000100540216577261704E6F6E457863657074696F6E5468726F777301000000000011E9245000000000020000009800000080270000800900005253445319EFCE3EB3B5BD46990E97B75770543D07000000633A5C75736572735C6372616967775C646F63756D656E74735C76697375616C2073747564696F20323031305C50726F6A656374735C5472696D547261696C696E674C656164696E675C5472696D547261696C696E674C656164696E675C6F626A5C44656275675C5472696D547261696C696E674C656164696E672E706462004028000000000000000000005E2800000020000000000000000000000000000000000000000000005028000000000000000000000000000000005F436F72446C6C4D61696E006D73636F7265652E646C6C0000000000FF250020400000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000100100000001800008000000000000000000000000000000100010000003000008000000000000000000000000000000100000000004800000058400000300300000000000000000000300334000000560053005F00560045005200530049004F004E005F0049004E0046004F0000000000BD04EFFE0000010000000100084DFD1100000100084DFD113F000000000000000400000002000000000000000000000000000000440000000100560061007200460069006C00650049006E0066006F00000000002400040000005400720061006E0073006C006100740069006F006E00000000000000B00490020000010053007400720069006E006700460069006C00650049006E0066006F0000006C0200000100300030003000300030003400620030000000500014000100460069006C0065004400650073006300720069007000740069006F006E00000000005400720069006D0054007200610069006C0069006E0067004C0065006100640069006E006700000040000F000100460069006C006500560065007200730069006F006E000000000031002E0030002E0034003600300035002E00310039003700320030000000000050001800010049006E007400650072006E0061006C004E0061006D00650000005400720069006D0054007200610069006C0069006E0067004C0065006100640069006E0067002E0064006C006C0000004800120001004C006500670061006C0043006F007000790072006900670068007400000043006F0070007900720069006700680074002000A90020002000320030003100320000005800180001004F0072006900670069006E0061006C00460069006C0065006E0061006D00650000005400720069006D0054007200610069006C0069006E0067004C0065006100640069006E0067002E0064006C006C000000480014000100500072006F0064007500630074004E0061006D006500000000005400720069006D0054007200610069006C0069006E0067004C0065006100640069006E006700000044000F000100500072006F006400750063007400560065007200730069006F006E00000031002E0030002E0034003600300035002E00310039003700320030000000000048000F00010041007300730065006D0062006C0079002000560065007200730069006F006E00000031002E0030002E0034003600300035002E003100390037003200300000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000002000000C000000703800000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
WITH PERMISSION_SET = SAFE;
GO
CREATE FUNCTION [dbo].[Trim](@inputString [nvarchar](4000))
RETURNS [nvarchar](4000) WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [TrimTrailingLeading].[TrimTrailingLeading.UserDefinedFunctions].[Trim];
SET NOCOUNT ON;IF object_id('tempdb..#sampleData') IS NOT NULL
BEGIN
DROP TABLE #sampleData;
END;
--1,000,000 Random rows of data
SELECT TOP 1000000 IDENTITY(INT,1,1) AS [Sno],
'0000' + CAST((ABS(CHECKSUM(NEWID())) % 20000) + 1 AS VARCHAR(20)) + '0' AS [Column]
INTO #sampleData
FROM master.dbo.syscolumns sc1, master.dbo.syscolumns sc2, master.dbo.syscolumns sc3;
DECLARE @HOLDER VARCHAR(20);
PRINT REPLICATE('=',80);
PRINT 'CLR 4';
PRINT REPLICATE('=',80);
SET STATISTICS TIME ON;
SELECT @HOLDER = [dbo].[Trim]([Column])
FROM #sampleData
SET STATISTICS TIME OFF;
================================================================================CLR 4
================================================================================
SQL Server Execution Times:
CPU time = 3562 ms, elapsed time = 3966 ms.
Still slower than all of the pure t-sql version, but a considerable improvement on my attempts at a regex replace.
Eugene Elutin (8/10/2012)
AND NOT USE TRY TO CATCH THE ERROR!!!The shouting is unappreciated and unnecessary. I'm running these tests out of curiosity, not to be verbally attacked.
Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Viewing 15 posts - 16 through 30 (of 36 total)
You must be logged in to reply to this topic. Login to reply