Converting first letter of every word to uppercase

  • I'm converting an Access db to SQL and one of the queries converts the first letter of each word to uppercase using the StrConv function in access. I found that there isn't a corresponding function in SQL. Below is the query which is simple just need to know what to use to make this happen

    UPDATE Attribute_Data

    SET Attribute_Data.AttLabel = StrConv([AttLabel],3)

    WHERE (((Attribute_Data.AttributeNum) Like '%mfg%'))

    Thanks,

    Newbie

  • Will Attribute_Value column contains mutilple words for every single row?

    Or is it going to be only one word for all the rows in that column?

  • The AttLabel field will have multiple words in it.

  • As far as i know, we dont have any camel-casing utility function in T-SQL.

    Here is my stab at it

    DECLARE @Table TABLE

    (

    iD INT

    ,AttrVal1 VARCHAR(100)

    ,AttrVal2 VARCHAR(100)

    )

    INSERT INTO @Table

    SELECT 1 , 'eXEC' ,'exec Proc myproc'

    UNION ALL SELECT 2 , 'Sql' ,'Exec Proc Myproc2'

    UNION ALL SELECT 3 , 'tsql' ,'CREATE FUNCTION A'

    UNION ALL SELECT 4 , 'BCP' ,'bulk upload all files'

    UNION ALL SELECT 5 , ' xp_cmdshell ' ,' dir /*'

    ; WITH CTE AS

    (

    SELECT T.iD

    , T.AttrVal1

    , T.AttrVal2

    , UprCaseAttVal1 = STUFF(CrsApp1.AttVal1 ,1,1,UPPER(LEFT(CrsApp1.AttVal1,1)))

    , UprCaseAttVal2 = STUFF(CrsApp2.Item ,1,1,UPPER(LEFT(CrsApp2.Item,1)))

    , CrsApp2.ItemNumber

    FROM @Table T

    CROSS APPLY (SELECT RTRIM(LTRIM(T.AttrVal1)) , RTRIM(LTRIM(T.AttrVal2)) ) CrsApp1 (AttVal1,AttVal2)

    CROSS APPLY dbo.DelimitedSplit8K (CrsApp1.AttVal2,' ') CrsApp2

    )

    SELECT C.iD

    ,AttrVal1 = C.UprCaseAttVal1

    ,AttrVal2 = STUFF ( ( SELECT ' '+C1.UprCaseAttVal2

    FROM CTE C1

    WHERE c1.iD = C.id

    ORDER BY C1.ItemNumber

    FOR XML PATH(''),TYPE

    ).value('.','VARCHAR(MAX)') , 1,1,SPACE(0))

    FROM CTE C

    GROUP BY C.iD , C.UprCaseAttVal1

  • If you look at the sample data in @Table, AttrVal1 column has a single word entry for all rows while the second column has multiple words for every row.

    I have also used a string splitter function dbo.DelimitedSplit8k, created by our very own Jeff Moden, which , you can find here[/url]

    Let us know if you need any help.

  • Your example is way far from my knowledge of SQL. I am going to try to see if I can understand what you are doing.

    I appreciate your help.

  • .NET has this functionality built-in and does a pretty good job. It may not be exactly what you want, but I thought it was worth mentioning. Here is a SQLCLR function you can add to your system to try:

    CREATE ASSEMBLY [SqlClrCase]

    AUTHORIZATION [dbo]

    FROM 0x4D5A90000300000004000000FFFF0000B800000000000000400000000000000000000000000000000000000000000000000000000000000000000000800000000E1FBA0E00B409CD21B8014CCD21546869732070726F6772616D2063616E6E6F742062652072756E20696E20444F53206D6F64652E0D0D0A2400000000000000504500004C010300AE61AD4F0000000000000000E00002210B0108000008000000060000000000008E2700000020000000400000000040000020000000020000040000000000000004000000000000000080000000020000000000000300408500001000001000000000100000100000000000001000000000000000000000003C2700004F00000000400000D003000000000000000000000000000000000000006000000C000000CC2600001C0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000200000080000000000000000000000082000004800000000000000000000002E7465787400000094070000002000000008000000020000000000000000000000000000200000602E72737263000000D00300000040000000040000000A0000000000000000000000000000400000402E72656C6F6300000C0000000060000000020000000E0000000000000000000000000000400000420000000000000000000000000000000070270000000000004800000002000500902000003C060000010000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000013300200290000000100001100280F00000A6F1000000A0A060F00FE16020000016F1100000A6F1200000A731300000A0B2B00072A1E02281400000A2A00000042534A4201000100000000000C00000076322E302E35303732370000000005006C000000FC010000237E000068020000DC02000023537472696E6773000000004405000008000000235553004C0500001000000023475549440000005C050000E000000023426C6F620000000000000002000001471502000900000000FA2533001600000100000013000000020000000200000001000000140000000C00000001000000010000000200000000000A0001000000000006003E0037000A006600510006009A0088000600B10088000600CE0088000600ED00880006000601880006001F01880006003A01880006005501880006008D016E010600A10188000600CD01BA013700E101000006001002F00106003002F0010A007402590206009E0289020600BD02890200000000010000000000010001000100100019000000050001000100502000000000960070000A00010085200000000086187C001100020000000100820019007C00150021007C00150029007C00150031007C00150039007C00150041007C00150049007C00150051007C00150059007C001A0061007C00150069007C001F0079007C00250081007C00110089007C0011009100AA022F009100C60234000900D3023900990070003D0011007C00150009007C001100200073002A002E002B0049002E00130059002E001B0059002E0023005F002E000B0049002E0033007E002E003B0059002E004B0059002E005B00AF002E006300B8002E006B00C10042000480000001000000A2115F540000000000004E02000002000000000000000000000001002E000000000002000000000000000000000001004500000000000000003C4D6F64756C653E0053716C436C72436173652E646C6C0055736572446566696E656446756E6374696F6E73006D73636F726C69620053797374656D004F626A6563740053797374656D2E446174610053797374656D2E446174612E53716C54797065730053716C537472696E6700546F5469746C6543617365002E63746F720076616C75650053797374656D2E5265666C656374696F6E00417373656D626C795469746C6541747472696275746500417373656D626C794465736372697074696F6E41747472696275746500417373656D626C79436F6E66696775726174696F6E41747472696275746500417373656D626C79436F6D70616E7941747472696275746500417373656D626C7950726F6475637441747472696275746500417373656D626C79436F7079726967687441747472696275746500417373656D626C7954726164656D61726B41747472696275746500417373656D626C7943756C747572654174747269627574650053797374656D2E52756E74696D652E496E7465726F70536572766963657300436F6D56697369626C6541747472696275746500417373656D626C7956657273696F6E4174747269627574650053797374656D2E446961676E6F73746963730044656275676761626C6541747472696275746500446562756767696E674D6F6465730053797374656D2E52756E74696D652E436F6D70696C6572536572766963657300436F6D70696C6174696F6E52656C61786174696F6E734174747269627574650052756E74696D65436F6D7061746962696C6974794174747269627574650053716C436C7243617365004D6963726F736F66742E53716C5365727665722E5365727665720053716C46756E6374696F6E4174747269627574650053797374656D2E476C6F62616C697A6174696F6E0043756C74757265496E666F006765745F43757272656E7443756C747572650054657874496E666F006765745F54657874496E666F00546F537472696E67000003200000000000916A6CB2393D1540969444A6AD60E8440008B77A5C561934E0890600011109110903200001042001010E0420010102052001011139042001010804010000000400001249042000124D0320000E0420010E0E060702124D11090F01000A53716C436C724361736500000501000000001E010019436861726C657320536368776162202620436F2E2C496E632E00003001002B436F7079726967687420C2A920436861726C657320536368776162202620436F2E2C496E632E203230313200000801000701000000000801000800000000001E01000100540216577261704E6F6E457863657074696F6E5468726F77730100000000AE61AD4F000000000200000052000000E8260000E808000052534453E25A305088EDA14A90ECEF9CA5FAC61701000000433A5C405C53636877616253747566665F323031305C53716C436C72436173655C6F626A5C44656275675C53716C436C72436173652E7064620000006427000000000000000000007E27000000200000000000000000000000000000000000000000000070270000000000000000000000005F436F72446C6C4D61696E006D73636F7265652E646C6C0000000000FF250020400000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000100100000001800008000000000000000000000000000000100010000003000008000000000000000000000000000000100000000004800000058400000780300000000000000000000780334000000560053005F00560045005200530049004F004E005F0049004E0046004F0000000000BD04EFFE00000100000001005F54A211000001005F54A2113F000000000000000400000002000000000000000000000000000000440000000100560061007200460069006C00650049006E0066006F00000000002400040000005400720061006E0073006C006100740069006F006E00000000000000B004D8020000010053007400720069006E006700460069006C00650049006E0066006F000000B4020000010030003000300030003000340062003000000054001A00010043006F006D00700061006E0079004E0061006D0065000000000043006800610072006C0065007300200053006300680077006100620020002600200043006F002E002C0049006E0063002E00000040000B000100460069006C0065004400650073006300720069007000740069006F006E0000000000530071006C0043006C00720043006100730065000000000040000F000100460069006C006500560065007200730069006F006E000000000031002E0030002E0034003500310034002E00320031003500390039000000000040000F00010049006E007400650072006E0061006C004E0061006D0065000000530071006C0043006C00720043006100730065002E0064006C006C00000000007C002B0001004C006500670061006C0043006F007000790072006900670068007400000043006F0070007900720069006700680074002000A900200043006800610072006C0065007300200053006300680077006100620020002600200043006F002E002C0049006E0063002E00200032003000310032000000000048000F0001004F0072006900670069006E0061006C00460069006C0065006E0061006D0065000000530071006C0043006C00720043006100730065002E0064006C006C000000000038000B000100500072006F0064007500630074004E0061006D00650000000000530071006C0043006C00720043006100730065000000000044000F000100500072006F006400750063007400560065007200730069006F006E00000031002E0030002E0034003500310034002E00320031003500390039000000000048000F00010041007300730065006D0062006C0079002000560065007200730069006F006E00000031002E0030002E0034003500310034002E003200310035003900390000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000002000000C000000903700000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000

    WITH PERMISSION_SET = SAFE;

    CREATE FUNCTION [dbo].[ToTitleCase]

    (@value NVARCHAR (4000))

    RETURNS NVARCHAR (4000)

    AS

    EXTERNAL NAME [SqlClrCase].[UserDefinedFunctions].[ToTitleCase]

    -- Test it out:

    SELECT dbo.ToTitleCase('The quick brown fox JUMPS over the lazy dog.');

    Note: you will need to enable SQLCLR before it can be used:

    EXEC SP_CONFIGURE 'show advanced options' , '1';

    GO

    RECONFIGURE;

    GO

    EXEC SP_CONFIGURE 'clr enabled' , '1'

    GO

    RECONFIGURE;

    GO

    Here is the source code for the SQLCLR function. You can see it is a built in method call on the TextInfo object to change a string to title case:

    using System;

    using System.Data;

    using System.Data.SqlClient;

    using System.Data.SqlTypes;

    using Microsoft.SqlServer.Server;

    using System.Globalization;

    public partial class UserDefinedFunctions

    {

    [Microsoft.SqlServer.Server.SqlFunction]

    public static SqlString ToTitleCase(SqlString value)

    {

    TextInfo info = CultureInfo.CurrentCulture.TextInfo;

    return new SqlString(info.ToTitleCase(value.ToString()));

    }

    };

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • opc.three (5/11/2012)


    .NET has this functionality built-in and does a pretty good job. It may not be exactly what you want, but I thought it was worth mentioning. Here is a SQLCLR function you can add to your system to try:

    CREATE ASSEMBLY [SqlClrCase]

    AUTHORIZATION [dbo]

    FROM 0x4D5A90000300000004000000FFFF0000B800000000000000400000000000000000000000000000000000000000000000000000000000000000000000800000000E1FBA0E00B409CD21B8014CCD21546869732070726F6772616D2063616E6E6F742062652072756E20696E20444F53206D6F64652E0D0D0A2400000000000000504500004C010300AE61AD4F0000000000000000E00002210B0108000008000000060000000000008E2700000020000000400000000040000020000000020000040000000000000004000000000000000080000000020000000000000300408500001000001000000000100000100000000000001000000000000000000000003C2700004F00000000400000D003000000000000000000000000000000000000006000000C000000CC2600001C0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000200000080000000000000000000000082000004800000000000000000000002E7465787400000094070000002000000008000000020000000000000000000000000000200000602E72737263000000D00300000040000000040000000A0000000000000000000000000000400000402E72656C6F6300000C0000000060000000020000000E0000000000000000000000000000400000420000000000000000000000000000000070270000000000004800000002000500902000003C060000010000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000013300200290000000100001100280F00000A6F1000000A0A060F00FE16020000016F1100000A6F1200000A731300000A0B2B00072A1E02281400000A2A00000042534A4201000100000000000C00000076322E302E35303732370000000005006C000000FC010000237E000068020000DC02000023537472696E6773000000004405000008000000235553004C0500001000000023475549440000005C050000E000000023426C6F620000000000000002000001471502000900000000FA2533001600000100000013000000020000000200000001000000140000000C00000001000000010000000200000000000A0001000000000006003E0037000A006600510006009A0088000600B10088000600CE0088000600ED00880006000601880006001F01880006003A01880006005501880006008D016E010600A10188000600CD01BA013700E101000006001002F00106003002F0010A007402590206009E0289020600BD02890200000000010000000000010001000100100019000000050001000100502000000000960070000A00010085200000000086187C001100020000000100820019007C00150021007C00150029007C00150031007C00150039007C00150041007C00150049007C00150051007C00150059007C001A0061007C00150069007C001F0079007C00250081007C00110089007C0011009100AA022F009100C60234000900D3023900990070003D0011007C00150009007C001100200073002A002E002B0049002E00130059002E001B0059002E0023005F002E000B0049002E0033007E002E003B0059002E004B0059002E005B00AF002E006300B8002E006B00C10042000480000001000000A2115F540000000000004E02000002000000000000000000000001002E000000000002000000000000000000000001004500000000000000003C4D6F64756C653E0053716C436C72436173652E646C6C0055736572446566696E656446756E6374696F6E73006D73636F726C69620053797374656D004F626A6563740053797374656D2E446174610053797374656D2E446174612E53716C54797065730053716C537472696E6700546F5469746C6543617365002E63746F720076616C75650053797374656D2E5265666C656374696F6E00417373656D626C795469746C6541747472696275746500417373656D626C794465736372697074696F6E41747472696275746500417373656D626C79436F6E66696775726174696F6E41747472696275746500417373656D626C79436F6D70616E7941747472696275746500417373656D626C7950726F6475637441747472696275746500417373656D626C79436F7079726967687441747472696275746500417373656D626C7954726164656D61726B41747472696275746500417373656D626C7943756C747572654174747269627574650053797374656D2E52756E74696D652E496E7465726F70536572766963657300436F6D56697369626C6541747472696275746500417373656D626C7956657273696F6E4174747269627574650053797374656D2E446961676E6F73746963730044656275676761626C6541747472696275746500446562756767696E674D6F6465730053797374656D2E52756E74696D652E436F6D70696C6572536572766963657300436F6D70696C6174696F6E52656C61786174696F6E734174747269627574650052756E74696D65436F6D7061746962696C6974794174747269627574650053716C436C7243617365004D6963726F736F66742E53716C5365727665722E5365727665720053716C46756E6374696F6E4174747269627574650053797374656D2E476C6F62616C697A6174696F6E0043756C74757265496E666F006765745F43757272656E7443756C747572650054657874496E666F006765745F54657874496E666F00546F537472696E67000003200000000000916A6CB2393D1540969444A6AD60E8440008B77A5C561934E0890600011109110903200001042001010E0420010102052001011139042001010804010000000400001249042000124D0320000E0420010E0E060702124D11090F01000A53716C436C724361736500000501000000001E010019436861726C657320536368776162202620436F2E2C496E632E00003001002B436F7079726967687420C2A920436861726C657320536368776162202620436F2E2C496E632E203230313200000801000701000000000801000800000000001E01000100540216577261704E6F6E457863657074696F6E5468726F77730100000000AE61AD4F000000000200000052000000E8260000E808000052534453E25A305088EDA14A90ECEF9CA5FAC61701000000433A5C405C53636877616253747566665F323031305C53716C436C72436173655C6F626A5C44656275675C53716C436C72436173652E7064620000006427000000000000000000007E27000000200000000000000000000000000000000000000000000070270000000000000000000000005F436F72446C6C4D61696E006D73636F7265652E646C6C0000000000FF250020400000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000100100000001800008000000000000000000000000000000100010000003000008000000000000000000000000000000100000000004800000058400000780300000000000000000000780334000000560053005F00560045005200530049004F004E005F0049004E0046004F0000000000BD04EFFE00000100000001005F54A211000001005F54A2113F000000000000000400000002000000000000000000000000000000440000000100560061007200460069006C00650049006E0066006F00000000002400040000005400720061006E0073006C006100740069006F006E00000000000000B004D8020000010053007400720069006E006700460069006C00650049006E0066006F000000B4020000010030003000300030003000340062003000000054001A00010043006F006D00700061006E0079004E0061006D0065000000000043006800610072006C0065007300200053006300680077006100620020002600200043006F002E002C0049006E0063002E00000040000B000100460069006C0065004400650073006300720069007000740069006F006E0000000000530071006C0043006C00720043006100730065000000000040000F000100460069006C006500560065007200730069006F006E000000000031002E0030002E0034003500310034002E00320031003500390039000000000040000F00010049006E007400650072006E0061006C004E0061006D0065000000530071006C0043006C00720043006100730065002E0064006C006C00000000007C002B0001004C006500670061006C0043006F007000790072006900670068007400000043006F0070007900720069006700680074002000A900200043006800610072006C0065007300200053006300680077006100620020002600200043006F002E002C0049006E0063002E00200032003000310032000000000048000F0001004F0072006900670069006E0061006C00460069006C0065006E0061006D0065000000530071006C0043006C00720043006100730065002E0064006C006C000000000038000B000100500072006F0064007500630074004E0061006D00650000000000530071006C0043006C00720043006100730065000000000044000F000100500072006F006400750063007400560065007200730069006F006E00000031002E0030002E0034003500310034002E00320031003500390039000000000048000F00010041007300730065006D0062006C0079002000560065007200730069006F006E00000031002E0030002E0034003500310034002E003200310035003900390000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000002000000C000000903700000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000

    WITH PERMISSION_SET = SAFE;

    CREATE FUNCTION [dbo].[ToTitleCase]

    (@value NVARCHAR (4000))

    RETURNS NVARCHAR (4000)

    AS

    EXTERNAL NAME [SqlClrCase].[UserDefinedFunctions].[ToTitleCase]

    -- Test it out:

    SELECT dbo.ToTitleCase('The quick brown fox JUMPS over the lazy dog.');

    Note: you will need to enable SQLCLR before it can be used:

    EXEC SP_CONFIGURE 'show advanced options' , '1';

    GO

    RECONFIGURE;

    GO

    EXEC SP_CONFIGURE 'clr enabled' , '1'

    GO

    RECONFIGURE;

    GO

    Here is the source code for the SQLCLR function. You can see it is a built in method call on the TextInfo object to change a string to title case:

    using System;

    using System.Data;

    using System.Data.SqlClient;

    using System.Data.SqlTypes;

    using Microsoft.SqlServer.Server;

    using System.Globalization;

    public partial class UserDefinedFunctions

    {

    [Microsoft.SqlServer.Server.SqlFunction]

    public static SqlString ToTitleCase(SqlString value)

    {

    TextInfo info = CultureInfo.CurrentCulture.TextInfo;

    return new SqlString(info.ToTitleCase(value.ToString()));

    }

    };

    Do you have any performance metrics for this, Orlando?

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

  • Jeff Moden (5/11/2012)


    Do you have any performance metrics for this, Orlando?

    I do not, but I would be happy to mock up some test data and run some tests. I used the TextInfo class extensively in my .Net dev days, but never in the SQLCLR space. The weather turned cold here so golf became a no-go meaning I may have some time to look at this tomorrow AM.

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • opc.three (5/11/2012)


    Jeff Moden (5/11/2012)


    Do you have any performance metrics for this, Orlando?

    I do not, but I would be happy to mock up some test data and run some tests. I used the TextInfo class extensively in my .Net dev days, but never in the SQLCLR space. The weather turned cold here so golf became a no-go meaning I may have some time to look at this tomorrow AM.

    Actually, no need. You're one of the people in this world that I trust so I installed your nicely provided CLR and tested it. Like a CLR splitter, this bit of CLR code is about twice as fast as the fastest code I've been able to write. Takes the CLR an average of 642ms to clean and return 10K rows to the screen. Takes the fastest T-SQL I have an average of 1175ms to do the same thing.

    The gap gets quite a bit wider if you dump the output to a variable instead of the screen with the CLR generally coming in a 150ms and the T-SQL coming in at 820ms.

    This is one place where I'll be happy to admit superiority of the CLR.

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

  • Thank you for the vote of trust. Unlike the author(s?) of the CLR String Splitter, who no doubt spent a non-trivial amount of time developing, testing and tuning their object, I cannot take credit for any performance benefits offered by the CLR for this particular proper-case application. I only exposed an existing .Net Base Class Library method as a T-SQL Scalar UDF which is no great feat. I would have been happy with the syntax simplification if dbo.ToTitleCase offered even comparable performance and am elated it actually offers something of a performance advantage. Thank you for testing it and posting your findings.

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • opc.three (5/11/2012)


    Jeff Moden (5/11/2012)


    Do you have any performance metrics for this, Orlando?

    I do not, but I would be happy to mock up some test data and run some tests. I used the TextInfo class extensively in my .Net dev days, but never in the SQLCLR space. The weather turned cold here so golf became a no-go meaning I may have some time to look at this tomorrow AM.

    Having this "hidden gem" and if you go to the trouble of testing same .... I think it is worthy enough to be an article here on SCC, or if not wanting to go that far ... at the very least add it to the SQL SPACKLE entries.

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • bitbucket-25253 (5/12/2012)


    opc.three (5/11/2012)


    Jeff Moden (5/11/2012)


    Do you have any performance metrics for this, Orlando?

    I do not, but I would be happy to mock up some test data and run some tests. I used the TextInfo class extensively in my .Net dev days, but never in the SQLCLR space. The weather turned cold here so golf became a no-go meaning I may have some time to look at this tomorrow AM.

    Having this "hidden gem" and if you go to the trouble of testing same .... I think it is worthy enough to be an article here on SCC, or if not wanting to go that far ... at the very least add it to the SQL SPACKLE entries.

    I never gave it much thought, but now that you mention it I think you are spot on with adding it to the SQL Spackle series. I will give it a few more paces to see what quirks I can find so I can document "known issues" ahead of time. Provided it makes sense after running it down I agree it would be a nice complement to UPPER() and LOWER(). We are forced to reference UDFs using a schema so what do you think of dbo.PROPER() or dbo.TITLE()? Any other suggestions on naming, schema or object?

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • opc.three (5/14/2012)


    bitbucket-25253 (5/12/2012)


    opc.three (5/11/2012)


    Jeff Moden (5/11/2012)


    Do you have any performance metrics for this, Orlando?

    I do not, but I would be happy to mock up some test data and run some tests. I used the TextInfo class extensively in my .Net dev days, but never in the SQLCLR space. The weather turned cold here so golf became a no-go meaning I may have some time to look at this tomorrow AM.

    Having this "hidden gem" and if you go to the trouble of testing same .... I think it is worthy enough to be an article here on SCC, or if not wanting to go that far ... at the very least add it to the SQL SPACKLE entries.

    I never gave it much thought, but now that you mention it I think you are spot on with adding it to the SQL Spackle series. I will give it a few more paces to see what quirks I can find so I can document "known issues" ahead of time. Provided it makes sense after running it down I agree it would be a nice complement to UPPER() and LOWER(). We are forced to reference UDFs using a schema so what do you think of dbo.PROPER() or dbo.TITLE()? Any other suggestions on naming, schema or object?

    Might I be so bold as to suggest as a title "PROPERCASE().

    In my mind TITLE implies all text is formatted as UPPERCASE and that function already exists in SQL SERVER

    A word of caution google for "sql server uppercase first letter", the results might either be included in your spackle article, or maybe cause you to think twice about doing a spackle article. Of course with a display of EXECUTION PLANS and some experiments to determine time required to do short / lengthy strings it would be an EXCELLENT article

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • bitbucket-25253 (5/14/2012)


    opc.three (5/14/2012)


    bitbucket-25253 (5/12/2012)


    opc.three (5/11/2012)


    Jeff Moden (5/11/2012)


    Do you have any performance metrics for this, Orlando?

    I do not, but I would be happy to mock up some test data and run some tests. I used the TextInfo class extensively in my .Net dev days, but never in the SQLCLR space. The weather turned cold here so golf became a no-go meaning I may have some time to look at this tomorrow AM.

    Having this "hidden gem" and if you go to the trouble of testing same .... I think it is worthy enough to be an article here on SCC, or if not wanting to go that far ... at the very least add it to the SQL SPACKLE entries.

    I never gave it much thought, but now that you mention it I think you are spot on with adding it to the SQL Spackle series. I will give it a few more paces to see what quirks I can find so I can document "known issues" ahead of time. Provided it makes sense after running it down I agree it would be a nice complement to UPPER() and LOWER(). We are forced to reference UDFs using a schema so what do you think of dbo.PROPER() or dbo.TITLE()? Any other suggestions on naming, schema or object?

    Might I be so bold as to suggest as a title "PROPERCASE().

    In my mind TITLE implies all text is formatted as UPPERCASE and that function already exists in SQL SERVER

    A word of caution google for "sql server uppercase first letter", the results might either be included in your spackle article, or maybe cause you to think twice about doing a spackle article. Of course with a display of EXECUTION PLANS and some experiments to determine time required to do short / lengthy strings it would be an EXCELLENT article

    I did as you suggested and Googled the phrase. Interesting results. Some interpret "proper case" as "sentence case" meaning "the first letter of each sentence is uppercase and all else is lowercase". The function I posted offers up "the first letter of each word is uppercase and all letters following it in that word are lowercase, unless the whole word was already uppercase". The unless part may be a deal-breaker for most practical applications.

    Calling my example function like this:

    SELECT dbo.ToTitleCase('The quick brown fox JUMPS over the lazy dog.');

    Yields:

    The Quick Brown Fox JUMPS Over The Lazy Dog.

    JUMPS stays uppercase. I may decide to send everything to lowercase before passing the string into the .Net method. I think that would make the function more useful in general but I wonder how that would affect performance.

    Maybe the .Net architects named the TextInfo method ToTitleCase() to avoid confusion with "sentence case". Naming is so important with basic functionality which is why I wanted it open for discussion. I suppose anyone can pickup the function definition and simply rename it if they like, per their interpretation. There is plenty here for an article 😀

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

Viewing 15 posts - 1 through 15 (of 40 total)

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