Converting first letter of every word to uppercase

Question

Post reply

Converting first letter of every word to uppercase

tburk 5368

Mr or Mrs. 500

Points: 555
More actions
May 11, 2012 at 11:44 am

#273891

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

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

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

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 1

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?

tburk 5368 Mr or Mrs. 500 Points: 555 More actions · Answer 2

tburk 5368

Mr or Mrs. 500

Points: 555

May 11, 2012 at 11:57 am

#1486502

The AttLabel field will have multiple words in it.

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 3

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

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 4

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.

tburk 5368 Mr or Mrs. 500 Points: 555 More actions · Answer 5

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.

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 6

.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

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 7

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)

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 8

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

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 9

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)

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 10

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

Ron McCullough SSC Guru Points: 63877 More actions · Answer 11

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]

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 12

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

Ron McCullough SSC Guru Points: 63877 More actions · Answer 13

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]

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 14

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