First Tally Table T-SQL

  • 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

  • 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


    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • 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

  • 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.

  • 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 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


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • 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


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • 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);

  • Forum Etiquette: How to post Reporting Services problems
  • [/url]
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • [/url]
  • How to Post Performance Problems - by Gail Shaw
  • [/url]

  • 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;

  • 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);

  • Forum Etiquette: How to post Reporting Services problems
  • [/url]
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • [/url]
  • How to Post Performance Problems - by Gail Shaw
  • [/url]

  • 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 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);

  • Forum Etiquette: How to post Reporting Services problems
  • [/url]
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • [/url]
  • How to Post Performance Problems - by Gail Shaw
  • [/url]

  • 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


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • 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 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


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • Viewing 15 posts - 1 through 15 (of 21 total)

    You must be logged in to reply to this topic. Login to reply