Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12345»»»

Converting first letter of every word to uppercase Expand / Collapse
Author
Message
Posted Friday, May 11, 2012 11:44 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, May 16, 2012 9:40 AM
Points: 7, 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
Post #1298803
Posted Friday, May 11, 2012 11:48 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, September 12, 2014 5:21 PM
Points: 2,262, Visits: 5,406
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?
Post #1298806
Posted Friday, May 11, 2012 11:57 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, May 16, 2012 9:40 AM
Points: 7, Visits: 11
The AttLabel field will have multiple words in it.
Post #1298817
Posted Friday, May 11, 2012 12:12 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, September 12, 2014 5:21 PM
Points: 2,262, Visits: 5,406
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

Post #1298834
Posted Friday, May 11, 2012 12:16 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, September 12, 2014 5:21 PM
Points: 2,262, Visits: 5,406
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.
Post #1298836
Posted Friday, May 11, 2012 12:22 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, May 16, 2012 9:40 AM
Points: 7, 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.
Post #1298843
Posted Friday, May 11, 2012 1:05 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Monday, September 15, 2014 7:19 PM
Points: 7,127, Visits: 12,655
.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
Post #1298889
Posted Friday, May 11, 2012 7:35 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 9:16 PM
Points: 37,102, Visits: 31,655
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1299021
Posted Friday, May 11, 2012 8:53 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Monday, September 15, 2014 7:19 PM
Points: 7,127, Visits: 12,655
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
Post #1299035
Posted Friday, May 11, 2012 9:07 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 9:16 PM
Points: 37,102, Visits: 31,655
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1299037
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse