April 26, 2010 at 11:13 am
Hello,
I am attempting to use my first Tally Table and I'm not sure I am using the correct approach. I have a T-SQL loop that counts...something I thought prime for a Tally Table replacement.
DECLARE @StartTime DATETIME
SET @StartTime = GETDATE();
DECLARE @Position INT
DECLARE @data VARCHAR(8000)
SET @data = 'THIS IS ONE OF MANY REASONS WHY I DISLIKE THE AS400'
SELECT @data = STUFF(LOWER(@Data), 1, 1, UPPER(LEFT(@Data, 1))), @Position = PATINDEX('%[^a-zA-Z][a-z]%', @data COLLATE Latin1_General_Bin)
WHILE @Position > 0
Begin
SELECT @data = STUFF(@Data, @Position, 2, UPPER(SUBSTRING(@Data, @Position, 2))), @Position = PATINDEX('%[^a-zA-Z][a-z]%', @data COLLATE Latin1_General_Bin)
End
SELECT @data
SELECT STR(DATEDIFF(ms,@StartTime,GETDATE())) + ' Milliseconds duration'
However I feel as though this is one of those scenarios where a Tally Table is NOT a perfect replacement or, or more likely, I am heading down the wrong road which is why I am stopping to get some sagely advice. Here is my Tally Table approach:
DECLARE @StringTable TABLE
(
iPos INT IDENTITY(1,1),
Value VARCHAR(8000)
)
INSERT INTO @StringTable (Value)
SELECT SUBSTRING(@Data,N+1,1) + SUBSTRING(LOWER(@Data),N+2,1)
FROM dbo.Tally
WHERE N <= LEN(@Data)
AND SUBSTRING(@Data,N,1) = ' '
My thought was to split it into words and capitalize the first letter of each word but it seems I'm making it more convoluted and the performance will be lost by poor programming. The results of the above are below:
-------- ---------...
1 Is
2 On
3 Of
4 Ma
5 Re
6 Wh
7 I
8 Di
9 Th
10 As
(10 row(s) affected)
First I am NOT able to get at the first word because there is NO preceding space. Could I cocatenate one sure but doesn't seem right. Second now I have all these words in seperate rows how do I get them back into a single string to return.
Please edumakate me I'm feeling like I don't have the Tally Table down.
JB
April 26, 2010 at 12:00 pm
Hello,
One of the gurus on this site wrote this function for Delimiting strings. It works on strings up to 8000 chars in length.
IF OBJECT_ID('dbo.DelimitedSplit8K') IS NOT NULL DROP FUNCTION dbo.DelimitedSplit8K
GO
CREATE FUNCTION dbo.DelimitedSplit8K
/***************************************************************************************************
Purpose:
Split a given string at a given delimiter and return a list of the split elements (items).
Usage Example:
SELECT *
FROM dbo.DelimitedSplit8K(@StringToSplit, @Delimiter)
Returns:
iTVF containing the following:
ItemNumber = Element position of Item as a BIGINT (not converted to INT to eliminate a CAST)
Item = Element value as a VARCHAR(8000)
Notes:
1. Optimized for VARCHAR(8000) or less.
2. Optimized for single character delimiter.
3. Optimized for use with CROSS APPLY.
4. Does not "trim" elements just in case leading or trailing blanks are intended.
5. cteTally concept originally by Iztek Ben Gan and "decimalized" by Lynn Pettis (and others) for a
bit of extra speed and finally redacted by Jeff Moden for a different slant on readability and
compactness.
6. If you don't know how a Tally table can be used to replace loops, please see the following...
http://www.sqlservercentral.com/articles/T-SQL/62867/
Revision History:
Rev 00 - 20 Jan 2010 - Jeff Moden
- Base 10 redaction for CTE.
Rev 01 - 08 Mar 2010 - Jeff Moden
- Changed UNION ALL to UNPIVOT for bit of extra speed.
Rev 02 - 13 Mar 2010 - Jeff Moden
- Removed one additional concatenation and one subtraction from the SUBSTRING in the
SELECT List.
***************************************************************************************************/
--===== Define I/O parameters
(
@pString VARCHAR(8000),
@pDelimiter CHAR(1)
)
RETURNS TABLE
AS
RETURN
WITH --"Inline" CTE Driven "Tally Table" produces values up to 10K... enough to cover VARCHAR(8000).
E1(N) AS (SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1),--10
E2(N) AS (SELECT 1 FROM E1 a, E1 b), --100
E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10,000
cteTally(N) AS (SELECT ROW_NUMBER() OVER (ORDER BY N) FROM E4)
--===== Do the split
SELECT ROW_NUMBER() OVER (ORDER BY N) AS ItemNumber,
SUBSTRING(@pString, N, CHARINDEX(@pDelimiter, @pString + @pDelimiter, N) - N) AS Item
FROM cteTally
WHERE N < LEN(@pString) + 2
AND SUBSTRING(@pDelimiter + @pString, N, 1) = @pDelimiter
;
To utilize it with the code you were using:
select * from DelimitedSplit8K(@data, ' ') p;
And, to put it all back together again:
SELECT STUFF((SELECT ' ' + Item
FROM dbo.DelimitedSplit8K(@data, ' ') p
ORDER BY ItemNumber
FOR XML PATH ('')),1,1,'')
And to put it all back together with proper cased words:
SELECT STUFF((SELECT ' ' + UPPER(SubString(Item,1,1)) +
LOWER(SubString(Item,2,len(Item)))
FROM dbo.DelimitedSplit8K(@data, ' ') p
ORDER BY ItemNumber
FOR XML PATH ('')),1,1,'')
Edit: added proper casing of result
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
April 27, 2010 at 5:10 am
Well you certainly could do it that way - but it seems very inefficient to split, convert, and then concatenate again.
Much faster to do it all in one step, in one line of C#:
Functions:
-- Assembly
CREATE ASSEMBLY ScalarFunctions
AUTHORIZATION dbo
FROM 0x4D5A90000300000004000000FFFF0000B800000000000000400000000000000000000000000000000000000000000000000000000000000000000000800000000E1FBA0E00B409CD21B8014CCD21546869732070726F6772616D2063616E6E6F742062652072756E20696E20444F53206D6F64652E0D0D0A2400000000000000504500004C010300AEC3D64B0000000000000000E00002210B0108000010000000200000000000009E2800000020000000400000000040000020000000100000040000000000000004000000000000000080000000100000000000000300408500001000001000000000100000100000000000001000000000000000000000004428000057000000004000006803000000000000000000000000000000000000006000000C00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000200000080000000000000000000000082000004800000000000000000000002E74657874000000A4080000002000000010000000100000000000000000000000000000200000602E7273726300000068030000004000000010000000200000000000000000000000000000400000402E72656C6F6300000C0000000060000000100000003000000000000000000000000000004000004200000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000080280000000000004800000002000500A82000009C0700000100000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000133003001900000001000011280F00000A6F1000000A0A0606026F1100000A6F1200000A2A000000133003001900000001000011280F00000A6F1000000A0A0606026F1100000A6F1200000A2A1E02281300000A2A00000042534A4201000100000000000C00000076322E302E35303732370000000005006C00000024020000237E000090020000B402000023537472696E6773000000004405000008000000235553004C0500001000000023475549440000005C0500004002000023426C6F620000000000000002000001471502000900000000FA0133001600000100000011000000020000000300000004000000130000001000000001000000010000000200000000000A00010000000000060043003C0006007D006B000600B50096000600C9006B000600E2006B000600FD006B00060018016B00060031016B0006004A016B00060069016B00060086016B000600BD019D010600DD019D010A00320217020A0047021702060074025F02060095025F020000000001000000000001000100010010001E00000005000100010050200000000096004A000A000100782000000000960059000A0003009D2000000000861865000F000500000000000000000001005902000000000000000001005902110065001300190065001800210065001300290065001300310065001300390065001300410065001300490065001300510065001300590065001300610065001D00690065000F00710065000F00790065000F00810080029C0181009E02A1018900AB02A60189005900A601090065000F0020006B002200240073006A012E002300E2012E00330000022E0063001E022E001300E2012E002B00E8012E003B00E2012E004300E2012E004B00E2012E00530000022E005B00150240006B002200440073006A0164007300B00184007300B001AB010480000001000000B90EBFA8000000000000FB010000020000000000000000000000010033000000000002000000000000000000000001000B020000000000000000003C4D6F64756C653E005363616C617246756E6374696F6E732E646C6C0055736572446566696E656446756E6374696F6E73006D73636F726C69620053797374656D004F626A65637400546F5469746C65436173654D415800546F5469746C6543617365002E63746F720053797374656D2E5265666C656374696F6E00417373656D626C7956657273696F6E4174747269627574650053797374656D2E52756E74696D652E496E7465726F70536572766963657300436F6D56697369626C6541747472696275746500417373656D626C7943756C7475726541747472696275746500417373656D626C7954726164656D61726B41747472696275746500417373656D626C79436F7079726967687441747472696275746500417373656D626C7950726F6475637441747472696275746500417373656D626C79436F6D70616E7941747472696275746500417373656D626C79436F6E66696775726174696F6E41747472696275746500417373656D626C794465736372697074696F6E41747472696275746500417373656D626C795469746C654174747269627574650053797374656D2E52756E74696D652E436F6D70696C6572536572766963657300436F6D70696C6174696F6E52656C61786174696F6E734174747269627574650052756E74696D65436F6D7061746962696C697479417474726962757465005363616C617246756E6374696F6E730053797374656D2E44617461004D6963726F736F66742E53716C5365727665722E5365727665720053716C46756E6374696F6E4174747269627574650053716C466163657441747472696275746500496E7075740053797374656D2E476C6F62616C697A6174696F6E0043756C74757265496E666F006765745F496E76617269616E7443756C747572650054657874496E666F006765745F54657874496E666F00546F4C6F776572000000032000000000000B9FB364254D43469909A415EF8789010008B77A5C561934E0890400010E0E03200001042001010E042001010204200101088146010004005455794D6963726F736F66742E53716C5365727665722E5365727665722E446174614163636573734B696E642C2053797374656D2E446174612C2056657273696F6E3D322E302E302E302C2043756C747572653D6E65757472616C2C205075626C69634B6579546F6B656E3D623737613563353631393334653038390A446174614163636573730000000054557F4D6963726F736F66742E53716C5365727665722E5365727665722E53797374656D446174614163636573734B696E642C2053797374656D2E446174612C2056657273696F6E3D322E302E302E302C2043756C747572653D6E65757472616C2C205075626C69634B6579546F6B656E3D623737613563353631393334653038391053797374656D446174614163636573730000000054020F497344657465726D696E6973746963015402094973507265636973650131010003005408074D617853697A65FFFFFFFF54020A49734E756C6C61626C650054020D497346697865644C656E67746800040000124104200012450420010E0E040701124531010003005408074D617853697A65A00F000054020A49734E756C6C61626C650054020D497346697865644C656E6774680005010000000017010012436F7079726967687420C2A920203230313000001401000F5363616C617246756E6374696F6E7300000801000800000000001E01000100540216577261704E6F6E457863657074696F6E5468726F7773010000006C28000000000000000000008E280000002000000000000000000000000000000000000000000000802800000000000000000000000000000000000000005F436F72446C6C4D61696E006D73636F7265652E646C6C0000000000FF250020400000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000100100000001800008000000000000000000000000000000100010000003000008000000000000000000000000000000100000000004800000058400000100300000000000000000000100334000000560053005F00560045005200530049004F004E005F0049004E0046004F0000000000BD04EFFE0000010000000100BFA8B90E00000100BFA8B90E3F000000000000000400000002000000000000000000000000000000440000000100560061007200460069006C00650049006E0066006F00000000002400040000005400720061006E0073006C006100740069006F006E00000000000000B00470020000010053007400720069006E006700460069006C00650049006E0066006F0000004C0200000100300030003000300030003400620030000000480010000100460069006C0065004400650073006300720069007000740069006F006E00000000005300630061006C0061007200460075006E006300740069006F006E007300000040000F000100460069006C006500560065007200730069006F006E000000000031002E0030002E0033003700360039002E00340033003100390039000000000048001400010049006E007400650072006E0061006C004E0061006D00650000005300630061006C0061007200460075006E006300740069006F006E0073002E0064006C006C0000004800120001004C006500670061006C0043006F007000790072006900670068007400000043006F0070007900720069006700680074002000A90020002000320030003100300000005000140001004F0072006900670069006E0061006C00460069006C0065006E0061006D00650000005300630061006C0061007200460075006E006300740069006F006E0073002E0064006C006C000000400010000100500072006F0064007500630074004E0061006D006500000000005300630061006C0061007200460075006E006300740069006F006E007300000044000F000100500072006F006400750063007400560065007200730069006F006E00000031002E0030002E0033003700360039002E00340033003100390039000000000048000F00010041007300730065006D0062006C0079002000560065007200730069006F006E00000031002E0030002E0033003700360039002E0034003300310039003900000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000002000000C000000A038000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
WITH PERMISSION_SET = SAFE;
GO
-- For strings up to 8000 bytes
CREATE FUNCTION dbo.ToTitleCase
(
@Input NVARCHAR(4000)
)
RETURNS NVARCHAR(4000)
WITH EXECUTE AS CALLER
AS EXTERNAL NAME ScalarFunctions.UserDefinedFunctions.ToTitleCase;
GO
-- For LOB strings
CREATE FUNCTION dbo.ToTitleCaseMAX
(
@Input NVARCHAR(MAX)
)
RETURNS NVARCHAR(MAX)
WITH EXECUTE AS CALLER
AS EXTERNAL NAME ScalarFunctions.UserDefinedFunctions.ToTitleCaseMAX;
Tests:
-- Test 1
SELECT dbo.ToTitleCase(N'THIS IS ONE OF MANY REASONS WHY I DISLIKE THE AS400');
GO
-- Test 2
SELECT dbo.ToTitleCaseMAX(definition)
FROM msdb.sys.sql_modules;
GO
Source code:
using Microsoft.SqlServer.Server;
using System.Globalization;
public partial class UserDefinedFunctions
{
[Microsoft.SqlServer.Server.SqlFunction
(
DataAccess=DataAccessKind.None,
SystemDataAccess=SystemDataAccessKind.None,
IsDeterministic=true,
IsPrecise=true
)
]
[return: SqlFacet (MaxSize=-1, IsNullable=false, IsFixedLength=false)]
public static string ToTitleCaseMAX
(
[SqlFacet (MaxSize=-1, IsNullable=false, IsFixedLength=false)]
string Input
)
{
TextInfo ti = CultureInfo.InvariantCulture.TextInfo;
return ti.ToTitleCase(ti.ToLower(Input));
}
[Microsoft.SqlServer.Server.SqlFunction
(
DataAccess = DataAccessKind.None,
SystemDataAccess = SystemDataAccessKind.None,
IsDeterministic = true,
IsPrecise = true
)
]
[return: SqlFacet(MaxSize = 4000, IsNullable = false, IsFixedLength = false)]
public static string ToTitleCase
(
[SqlFacet(MaxSize = 4000, IsNullable = false, IsFixedLength = false)]
string Input
)
{
TextInfo ti = CultureInfo.InvariantCulture.TextInfo;
return ti.ToTitleCase(ti.ToLower(Input));
}
};
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
April 27, 2010 at 5:37 am
I agree an assembly would be the best approach. Why recreate the wheel and an assembly would be more globally universal for others to make use of as well. BUT our dba's are saying we can't do assemblies because of our Security Contract with the government there is to be nothing "installed" on the boxes. I don't personally buy this (I think the dba's don't have knowledge / experience of what I'm talking about) but it is a brick wall (the dba's) I won't be breaking through for now.
April 27, 2010 at 5:51 am
nfs_john (4/27/2010)
I agree an assembly would be the best approach. Why recreate the wheel and an assembly would be more globally universal for others to make use of as well. BUT our dba's are saying we can't do assemblies because of our Security Contract with the government there is to be nothing "installed" on the boxes. I don't personally buy this (I think the dba's don't have knowledge / experience of what I'm talking about) but it is a brick wall (the dba's) I won't be breaking through for now.
You're so right - and it saddens me greatly that so many DBAs are so backward about SQLCLR.
Much of it is defensiveness, arrogance, or ignorance. Ah well.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
May 13, 2010 at 5:39 pm
Paul White NZ (4/27/2010)
Well you certainly could do it that way - but it seems very inefficient to split, convert, and then concatenate again.Much faster to do it all in one step, in one line of C#:
Functions:
-- Assembly
CREATE ASSEMBLY ScalarFunctions
AUTHORIZATION dbo
FROM 0x4D5A90000300000004000000FFFF0000B800000000000000400000000000000000000000000000000000000000000000000000000000000000000000800000000E1FBA0E00B409CD21B8014CCD21546869732070726F6772616D2063616E6E6F742062652072756E20696E20444F53206D6F64652E0D0D0A2400000000000000504500004C010300AEC3D64B0000000000000000E00002210B0108000010000000200000000000009E2800000020000000400000000040000020000000100000040000000000000004000000000000000080000000100000000000000300408500001000001000000000100000100000000000001000000000000000000000004428000057000000004000006803000000000000000000000000000000000000006000000C00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000200000080000000000000000000000082000004800000000000000000000002E74657874000000A4080000002000000010000000100000000000000000000000000000200000602E7273726300000068030000004000000010000000200000000000000000000000000000400000402E72656C6F6300000C0000000060000000100000003000000000000000000000000000004000004200000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000080280000000000004800000002000500A82000009C0700000100000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000133003001900000001000011280F00000A6F1000000A0A0606026F1100000A6F1200000A2A000000133003001900000001000011280F00000A6F1000000A0A0606026F1100000A6F1200000A2A1E02281300000A2A00000042534A4201000100000000000C00000076322E302E35303732370000000005006C00000024020000237E000090020000B402000023537472696E6773000000004405000008000000235553004C0500001000000023475549440000005C0500004002000023426C6F620000000000000002000001471502000900000000FA0133001600000100000011000000020000000300000004000000130000001000000001000000010000000200000000000A00010000000000060043003C0006007D006B000600B50096000600C9006B000600E2006B000600FD006B00060018016B00060031016B0006004A016B00060069016B00060086016B000600BD019D010600DD019D010A00320217020A0047021702060074025F02060095025F020000000001000000000001000100010010001E00000005000100010050200000000096004A000A000100782000000000960059000A0003009D2000000000861865000F000500000000000000000001005902000000000000000001005902110065001300190065001800210065001300290065001300310065001300390065001300410065001300490065001300510065001300590065001300610065001D00690065000F00710065000F00790065000F00810080029C0181009E02A1018900AB02A60189005900A601090065000F0020006B002200240073006A012E002300E2012E00330000022E0063001E022E001300E2012E002B00E8012E003B00E2012E004300E2012E004B00E2012E00530000022E005B00150240006B002200440073006A0164007300B00184007300B001AB010480000001000000B90EBFA8000000000000FB010000020000000000000000000000010033000000000002000000000000000000000001000B020000000000000000003C4D6F64756C653E005363616C617246756E6374696F6E732E646C6C0055736572446566696E656446756E6374696F6E73006D73636F726C69620053797374656D004F626A65637400546F5469746C65436173654D415800546F5469746C6543617365002E63746F720053797374656D2E5265666C656374696F6E00417373656D626C7956657273696F6E4174747269627574650053797374656D2E52756E74696D652E496E7465726F70536572766963657300436F6D56697369626C6541747472696275746500417373656D626C7943756C7475726541747472696275746500417373656D626C7954726164656D61726B41747472696275746500417373656D626C79436F7079726967687441747472696275746500417373656D626C7950726F6475637441747472696275746500417373656D626C79436F6D70616E7941747472696275746500417373656D626C79436F6E66696775726174696F6E41747472696275746500417373656D626C794465736372697074696F6E41747472696275746500417373656D626C795469746C654174747269627574650053797374656D2E52756E74696D652E436F6D70696C6572536572766963657300436F6D70696C6174696F6E52656C61786174696F6E734174747269627574650052756E74696D65436F6D7061746962696C697479417474726962757465005363616C617246756E6374696F6E730053797374656D2E44617461004D6963726F736F66742E53716C5365727665722E5365727665720053716C46756E6374696F6E4174747269627574650053716C466163657441747472696275746500496E7075740053797374656D2E476C6F62616C697A6174696F6E0043756C74757265496E666F006765745F496E76617269616E7443756C747572650054657874496E666F006765745F54657874496E666F00546F4C6F776572000000032000000000000B9FB364254D43469909A415EF8789010008B77A5C561934E0890400010E0E03200001042001010E042001010204200101088146010004005455794D6963726F736F66742E53716C5365727665722E5365727665722E446174614163636573734B696E642C2053797374656D2E446174612C2056657273696F6E3D322E302E302E302C2043756C747572653D6E65757472616C2C205075626C69634B6579546F6B656E3D623737613563353631393334653038390A446174614163636573730000000054557F4D6963726F736F66742E53716C5365727665722E5365727665722E53797374656D446174614163636573734B696E642C2053797374656D2E446174612C2056657273696F6E3D322E302E302E302C2043756C747572653D6E65757472616C2C205075626C69634B6579546F6B656E3D623737613563353631393334653038391053797374656D446174614163636573730000000054020F497344657465726D696E6973746963015402094973507265636973650131010003005408074D617853697A65FFFFFFFF54020A49734E756C6C61626C650054020D497346697865644C656E67746800040000124104200012450420010E0E040701124531010003005408074D617853697A65A00F000054020A49734E756C6C61626C650054020D497346697865644C656E6774680005010000000017010012436F7079726967687420C2A920203230313000001401000F5363616C617246756E6374696F6E7300000801000800000000001E01000100540216577261704E6F6E457863657074696F6E5468726F7773010000006C28000000000000000000008E280000002000000000000000000000000000000000000000000000802800000000000000000000000000000000000000005F436F72446C6C4D61696E006D73636F7265652E646C6C0000000000FF250020400000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000100100000001800008000000000000000000000000000000100010000003000008000000000000000000000000000000100000000004800000058400000100300000000000000000000100334000000560053005F00560045005200530049004F004E005F0049004E0046004F0000000000BD04EFFE0000010000000100BFA8B90E00000100BFA8B90E3F000000000000000400000002000000000000000000000000000000440000000100560061007200460069006C00650049006E0066006F00000000002400040000005400720061006E0073006C006100740069006F006E00000000000000B00470020000010053007400720069006E006700460069006C00650049006E0066006F0000004C0200000100300030003000300030003400620030000000480010000100460069006C0065004400650073006300720069007000740069006F006E00000000005300630061006C0061007200460075006E006300740069006F006E007300000040000F000100460069006C006500560065007200730069006F006E000000000031002E0030002E0033003700360039002E00340033003100390039000000000048001400010049006E007400650072006E0061006C004E0061006D00650000005300630061006C0061007200460075006E006300740069006F006E0073002E0064006C006C0000004800120001004C006500670061006C0043006F007000790072006900670068007400000043006F0070007900720069006700680074002000A90020002000320030003100300000005000140001004F0072006900670069006E0061006C00460069006C0065006E0061006D00650000005300630061006C0061007200460075006E006300740069006F006E0073002E0064006C006C000000400010000100500072006F0064007500630074004E0061006D006500000000005300630061006C0061007200460075006E006300740069006F006E007300000044000F000100500072006F006400750063007400560065007200730069006F006E00000031002E0030002E0033003700360039002E00340033003100390039000000000048000F00010041007300730065006D0062006C0079002000560065007200730069006F006E00000031002E0030002E0033003700360039002E0034003300310039003900000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000002000000C000000A038000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
WITH PERMISSION_SET = SAFE;
GO
-- For strings up to 8000 bytes
CREATE FUNCTION dbo.ToTitleCase
(
@Input NVARCHAR(4000)
)
RETURNS NVARCHAR(4000)
WITH EXECUTE AS CALLER
AS EXTERNAL NAME ScalarFunctions.UserDefinedFunctions.ToTitleCase;
GO
-- For LOB strings
CREATE FUNCTION dbo.ToTitleCaseMAX
(
@Input NVARCHAR(MAX)
)
RETURNS NVARCHAR(MAX)
WITH EXECUTE AS CALLER
AS EXTERNAL NAME ScalarFunctions.UserDefinedFunctions.ToTitleCaseMAX;
Tests:
-- Test 1
SELECT dbo.ToTitleCase(N'THIS IS ONE OF MANY REASONS WHY I DISLIKE THE AS400');
GO
-- Test 2
SELECT dbo.ToTitleCaseMAX(definition)
FROM msdb.sys.sql_modules;
GO
Source code:
using Microsoft.SqlServer.Server;
using System.Globalization;
public partial class UserDefinedFunctions
{
[Microsoft.SqlServer.Server.SqlFunction
(
DataAccess=DataAccessKind.None,
SystemDataAccess=SystemDataAccessKind.None,
IsDeterministic=true,
IsPrecise=true
)
]
[return: SqlFacet (MaxSize=-1, IsNullable=false, IsFixedLength=false)]
public static string ToTitleCaseMAX
(
[SqlFacet (MaxSize=-1, IsNullable=false, IsFixedLength=false)]
string Input
)
{
TextInfo ti = CultureInfo.InvariantCulture.TextInfo;
return ti.ToTitleCase(ti.ToLower(Input));
}
[Microsoft.SqlServer.Server.SqlFunction
(
DataAccess = DataAccessKind.None,
SystemDataAccess = SystemDataAccessKind.None,
IsDeterministic = true,
IsPrecise = true
)
]
[return: SqlFacet(MaxSize = 4000, IsNullable = false, IsFixedLength = false)]
public static string ToTitleCase
(
[SqlFacet(MaxSize = 4000, IsNullable = false, IsFixedLength = false)]
string Input
)
{
TextInfo ti = CultureInfo.InvariantCulture.TextInfo;
return ti.ToTitleCase(ti.ToLower(Input));
}
};
Paul
What were the results on, say, a million rows?
--Jeff Moden
Change is inevitable... Change for the better is not.
May 13, 2010 at 5:58 pm
Paul White NZ (4/27/2010)
nfs_john (4/27/2010)
I agree an assembly would be the best approach. Why recreate the wheel and an assembly would be more globally universal for others to make use of as well. BUT our dba's are saying we can't do assemblies because of our Security Contract with the government there is to be nothing "installed" on the boxes. I don't personally buy this (I think the dba's don't have knowledge / experience of what I'm talking about) but it is a brick wall (the dba's) I won't be breaking through for now.You're so right - and it saddens me greatly that so many DBAs are so backward about SQLCLR.
Much of it is defensiveness, arrogance, or ignorance. Ah well.
Heh... YOU, Mr White, write excellent code. However, you and I both know that's an exception rather than the rule. The reason why a lot of DBA's shy away from SQLCLR's is because they've seen what "developers" have done to the performance of the database already and don't want to open up yet another avenue for absolute crap code to make its way into the fabric of the database.
Do I think that SQLCLR is a good thing? Absolutely! Contrary to popular belief, I was (still am, actually) thrilled when it first came out. Would I allow most "developers" I personally know use it? BWAA-HAAA!!!... absolutely NOT! 😉 It's not out of supposed FUD, arrogance, or ignorance... it's because I know and have seen what the "developers" do like the clown that couldn't figure out how to do a "merge" in T-SQL or the moroff that didn't know how to do a simple modulo... they both submitted SQLCLR code to "solve" the problem.
Now, if a DBA has a C# programmer that (s)he can actually trust, that may be a different story. 😀
--Jeff Moden
Change is inevitable... Change for the better is not.
May 13, 2010 at 6:55 pm
I couldn't help it - I like trying out things....:-P
CREATE FUNCTION [dbo].[ProperCase](@text VARCHAR(8000))
RETURNS TABLE
AS
RETURN(
SELECT
A.B.value('(./text())[1]','varchar(8000)') AS [TEXT]
FROM (
SELECT ''+
CASE
WHEN SUBSTRING(@text,N-1,1) IN ('',' ',CHAR(10),CHAR(13),CHAR(9),'.','!','(')
THEN UPPER(SUBSTRING(@text,N,1))
ELSE LOWER(SUBSTRING(@text,N,1))
END
FROM Tally
WHERE N>0 AND N<=LEN(@text)
FOR XML PATH(''),TYPE
) A(B)
)
It is a bit slow, (1000000 rows took about 52 seconds) but does work and is bound to end up nudging someone else to improve on it...
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);May 14, 2010 at 1:49 am
Jeff Moden (5/13/2010)
What were the results on, say, a million rows?
I downloaded The Complete Works of Shakespeare, split it into sentences and ran both routines on that. Turns out, there are 78,026 sentences 🙂
A typical run produced: timings of 929ms for the CLR routine, 24,741ms for Wayne's code, and 47,520ms for mister.magoo's.
CLR: 0.8s
Wayne: 16.4s
Magoo: 24.4s
Code used to load the sample data:
CREATE TABLE dbo.Shakespeare
(
text_data VARCHAR(4000) NOT NULL
);
BULK INSERT dbo.Shakespeare
FROM 'C:\temp\test\shaks12.txt'
WITH (
DATAFILETYPE = 'char',
CODEPAGE = 'RAW',
FIELDTERMINATOR = '',
ROWTERMINATOR = '.',
TABLOCK
);
Core test rig (slight change to Wayne's code to fix XML entitisation issues):
DECLARE @Bitbucket VARCHAR(4000);
SET STATISTICS TIME ON;
-- CLR
SELECT @Bitbucket =
dbo.ToTitleCase(S.text_data)
FROM dbo.Shakespeare S;
-- Wayne
SELECT @Bitbucket =
STUFF
(
(
SELECT ' ' + UPPER(SUBSTRING(Item,1,1)) +
LOWER(SUBSTRING(Item,2,LEN(Item)))
FROM dbo.DelimitedSplit8K(S.text_data, ' ') p
ORDER BY
ItemNumber
FOR XML PATH (''), TYPE
).value('./text()[1]', 'VARCHAR(4000)')
, 1, 1, '')
FROM dbo.Shakespeare S;
-- Magoo
SELECT @Bitbucket =
CA.TEXT
FROM dbo.Shakespeare S
CROSS
APPLY dbo.ProperCase(S.text_data) CA;
SET STATISTICS TIME OFF;
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
May 14, 2010 at 2:42 am
That sounds about right - the SQLCLR version is blindingly fast in my tests too....
Someone must be able to make a T-SQL version that comes close....Jeff....?
Oh and while I think of it, is there a way to make the DelimitedSplit function work with punctuation as well so that bad grammar is not penalised?
e.g. (Oh my! that's a tricky one...don't capitalise e.g.)
THIS SENTENCE IS OK.WHAT ABOUT THIS?AND THIS?
Should come out as (obviously I am not wanting to change the OP's requirements, just a side Q)
This Sentence Is Ok.What About This?And This?
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);May 14, 2010 at 3:32 am
mister.magoo (5/14/2010)
That sounds about right - the SQLCLR version is blindingly fast in my tests too....Someone must be able to make a T-SQL version that comes close....Jeff....?
I'd be amazed if there is a T-SQL solution that could get within an order of magnitude.
The SQLCLR routine is compiled native code. There's just no way that an interpreted language, doing all the work the hard way, is going to get close on this occasion.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
May 14, 2010 at 3:42 am
Paul White NZ (5/14/2010)
mister.magoo (5/14/2010)
That sounds about right - the SQLCLR version is blindingly fast in my tests too....Someone must be able to make a T-SQL version that comes close....Jeff....?I'd be amazed if there is a T-SQL solution that could get within an order of magnitude.
The SQLCLR routine is compiled native code. There's just no way that an interpreted language, doing all the work the hard way, is going to get close on this occasion.
No, fair point... but for those that cannot use SQLCLR it would be nice to get a bit closer...;-)
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);May 14, 2010 at 6:07 am
mister.magoo (5/14/2010)
Paul White NZ (5/14/2010)
mister.magoo (5/14/2010)
That sounds about right - the SQLCLR version is blindingly fast in my tests too....Someone must be able to make a T-SQL version that comes close....Jeff....?I'd be amazed if there is a T-SQL solution that could get within an order of magnitude.
The SQLCLR routine is compiled native code. There's just no way that an interpreted language, doing all the work the hard way, is going to get close on this occasion.
No, fair point... but for those that cannot use SQLCLR it would be nice to get a bit closer...;-)
I'm with Paul on this one (and on others, as well :-D). There are a lot of things that T-SQL is good for and I've come real close to beating SQLCLR even for some RegEx things in the past. But for string splits and many other types of string manipulation like this "initial caps" problem, no one is going to be able to touch an SQLCLR for performance especially when things like de-entitisation are used which nearly doubles the run time but is absolutely necessary for text like what Paul used. Of course, de-entitisation isn't necessary for numeric or "guaranteed" inputs, but T-SQL still won't be able to touch an SQLCLR such as that which Paul wrote.
As a continuation of what I said earlier in this thread, Paul and a couple of others like him would be in that rare and small circle of people I trust to write CLR's. For those DBA's that have a properly locked down system, a simple code review (notice that Paul always posts the source code for his SQLCLRs) with the Developer (no quotes this time ;-)) and a short demonstration on a test system should be all that's necessary to remove any fears. Developers should also understand that if the DBA is actually doing a good job, there will always be at least a code review with the DBA prior to anything coming close to his/her production system. A second set of eyes is never a bad thing and no one should ever take offense to a code review. Heh... to wit, even when I CAN promote my own code (like when I AM the DBA for a system), I don't do it without at least a peer review of the code (I usually have a peer review and a DBA review done even in a pinch... I'm human) just to be on the safe side.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 14, 2010 at 6:51 am
Hey Jeff,
As you know, there's nothing much in either of your replies that I don't agree with. 🙂
I set a pretty high bar for SQLCLR code that wants to be in a database I look after. The routine presented here might face a bit of a struggle: does code to format strings really belong in the database? I think I would be happier to see this sort of data-cleansing incorporated into the data load process (perhaps using SSIS).
I am all for using the capabilities of SQLCLR appropriately, but there are all too often deeper design issues in play.
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
June 27, 2010 at 9:10 pm
Paul White NZ (5/14/2010)
Hey Jeff,As you know, there's nothing much in either of your replies that I don't agree with. 🙂
I set a pretty high bar for SQLCLR code that wants to be in a database I look after. The routine presented here might face a bit of a struggle: does code to format strings really belong in the database? I think I would be happier to see this sort of data-cleansing incorporated into the data load process (perhaps using SSIS).
I am all for using the capabilities of SQLCLR appropriately, but there are all too often deeper design issues in play.
Paul
Man... sorry for the late reply. Lost track of this one.
In the great "Split Thread", testing showed that if you need to split the likes of "The Complete Works of Shakespeare" or anything else > 8 k bytes, that an SQLCLR is probably the way to go. For items under 8k, though, a decent SQL Split function performs almost as good as an SQLCLR.
The biggest "killer" is VARCHAR(MAX) which causes any "joined" code to take twice as long just due to the nature of the beast.
My other question would be, how often do folks need to split something like "Moby Dick" or "The Complete Works of Shakespeare"? Some claim "quite a lot" (I have a really hard time accepting that)... those folks should probably use the SQLCLR method. For the rest of the folks, perhaps not. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply