SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Converting first letter of every word to uppercase


Converting first letter of every word to uppercase

Author
Message
tburk 5368
tburk 5368
SSC Rookie
SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)

Group: General Forum Members
Points: 39 Visits: 11
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
ColdCoffee
ColdCoffee
Hall of Fame
Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)

Group: General Forum Members
Points: 3677 Visits: 5549
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
tburk 5368
SSC Rookie
SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)

Group: General Forum Members
Points: 39 Visits: 11
The AttLabel field will have multiple words in it.
ColdCoffee
ColdCoffee
Hall of Fame
Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)

Group: General Forum Members
Points: 3677 Visits: 5549
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
ColdCoffee
Hall of Fame
Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)

Group: General Forum Members
Points: 3677 Visits: 5549
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

Let us know if you need any help.
tburk 5368
tburk 5368
SSC Rookie
SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)

Group: General Forum Members
Points: 39 Visits: 11
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
Orlando Colamatteo
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14959 Visits: 14396
.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
Jeff Moden
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86117 Visits: 41096
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Orlando Colamatteo
Orlando Colamatteo
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14959 Visits: 14396
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
Jeff Moden
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86117 Visits: 41096
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search