Upper Case Lower Case

• rjjh78

  SSCrazy

  Points: 2787

  More actions

  May 1, 2018 at 9:14 am

  #403309

  Hello,
  I have a field that contains all capital letters.  For example, "SQL SERVER CENTRAL". 
  Is there a way I can update the field to read, "Sql Server Central"?

  
CREATE TABLE #t (ID int IDENTITY(1,1), FieldDesc varchar(25))
INSERT INTO #t (FieldDesc) VALUES ('SQL SERVER CENTRAL')
INSERT INTO #t (FieldDesc) VALUES ('JOHN DOE')

  SELECT ID, FieldDesc FROM #t
  

  -- Desired Results:
  -- 1 Sql Server Central
  -- 2 John Doe

  Many thanks in advance!
  

• KS

  Hall of Fame

  Points: 3323

  More actions

  May 1, 2018 at 9:37 am

  #1988801

  rjjh78 - Tuesday, May 1, 2018 9:14 AM

  Hello,
  I have a field that contains all capital letters.  For example, "SQL SERVER CENTRAL". 
  Is there a way I can update the field to read, "Sql Server Central"?

  
CREATE TABLE #t (ID int IDENTITY(1,1), FieldDesc varchar(25))
INSERT INTO #t (FieldDesc) VALUES ('SQL SERVER CENTRAL')
INSERT INTO #t (FieldDesc) VALUES ('JOHN DOE')

  SELECT ID, FieldDesc FROM #t
  

  -- Desired Results:
  -- 1 Sql Server Central
  -- 2 John Doe

  Many thanks in advance!
  

  Credit to ChisM@Work
  
UPDATE #T SET FieldDesc =
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
' ' + LOWER(FieldDesc) COLLATE LATIN1_GENERAL_BIN ,' z',' Z'),' y',' Y'),' x',' X'),' w',' W'),' v',' V'),' u',' U'),
' t',' T'),' s',' S'),' r',' R'),' q',' Q'),' p',' P'),' o',' O'),' n',' N'),' m',' M'),' l',' L'),' k',' K'),
' j',' J'),' i',' I'),' h',' H'),' g',' G'),' f',' F'),' e',' E'),' d',' D'),' c',' C'),' b',' B'),' a',' A')


• sgmunson

  SSC Guru

  Points: 110640

  More actions

  May 1, 2018 at 10:43 am

  #1988815

  Sowbhari - Tuesday, May 1, 2018 9:37 AM

  rjjh78 - Tuesday, May 1, 2018 9:14 AM

  Hello,
  I have a field that contains all capital letters.  For example, "SQL SERVER CENTRAL". 
  Is there a way I can update the field to read, "Sql Server Central"?

  
CREATE TABLE #t (ID int IDENTITY(1,1), FieldDesc varchar(25))
INSERT INTO #t (FieldDesc) VALUES ('SQL SERVER CENTRAL')
INSERT INTO #t (FieldDesc) VALUES ('JOHN DOE')

  SELECT ID, FieldDesc FROM #t
  

  -- Desired Results:
  -- 1 Sql Server Central
  -- 2 John Doe

  Many thanks in advance!
  

  Credit to ChisM@Work
  
UPDATE #T SET FieldDesc =
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
' ' + LOWER(FieldDesc) COLLATE LATIN1_GENERAL_BIN ,' z',' Z'),' y',' Y'),' x',' X'),' w',' W'),' v',' V'),' u',' U'),
' t',' T'),' s',' S'),' r',' R'),' q',' Q'),' p',' P'),' o',' O'),' n',' N'),' m',' M'),' l',' L'),' k',' K'),
' j',' J'),' i',' I'),' h',' H'),' g',' G'),' f',' F'),' e',' E'),' d',' D'),' c',' C'),' b',' B'),' a',' A')


  And what do you plan to do for entries such as CREATE TABLE #t (
    ID int IDENTITY(1,1),
    FieldDesc varchar(25)
);
INSERT INTO #t (FieldDesc)
    VALUES    ('SQL SERVER CENTRAL'),
            ('JOHN DOE'),
            ('DICK VANDYKE'),
            ('OLIVIA DEHAVILLAND'),
            ('NILS VANDERSLUICE');
/*
-- LAST 3 ENTRIES SHOULD BE:
--    Dick VanDyke
--    Olivia DeHavilland
--    Nils VanDerSluice
*/
  I know that someone once posted some kind of Proper function on this site at one point, but I don't have time to go search for it.   It took a LOT of those kinds of things into account.

  Steve (aka sgmunson) 🙂 🙂 🙂
  Rent Servers for Income (picks and shovels strategy)

• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  May 1, 2018 at 12:38 pm

  #1988846

  sgmunson - Tuesday, May 1, 2018 10:43 AM

  Sowbhari - Tuesday, May 1, 2018 9:37 AM

  rjjh78 - Tuesday, May 1, 2018 9:14 AM

  Hello,
  I have a field that contains all capital letters.  For example, "SQL SERVER CENTRAL". 
  Is there a way I can update the field to read, "Sql Server Central"?

  
CREATE TABLE #t (ID int IDENTITY(1,1), FieldDesc varchar(25))
INSERT INTO #t (FieldDesc) VALUES ('SQL SERVER CENTRAL')
INSERT INTO #t (FieldDesc) VALUES ('JOHN DOE')

  SELECT ID, FieldDesc FROM #t
  

  -- Desired Results:
  -- 1 Sql Server Central
  -- 2 John Doe

  Many thanks in advance!
  

  Credit to ChisM@Work
  
UPDATE #T SET FieldDesc =
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
' ' + LOWER(FieldDesc) COLLATE LATIN1_GENERAL_BIN ,' z',' Z'),' y',' Y'),' x',' X'),' w',' W'),' v',' V'),' u',' U'),
' t',' T'),' s',' S'),' r',' R'),' q',' Q'),' p',' P'),' o',' O'),' n',' N'),' m',' M'),' l',' L'),' k',' K'),
' j',' J'),' i',' I'),' h',' H'),' g',' G'),' f',' F'),' e',' E'),' d',' D'),' c',' C'),' b',' B'),' a',' A')


  And what do you plan to do for entries such as CREATE TABLE #t (
    ID int IDENTITY(1,1),
    FieldDesc varchar(25)
);
INSERT INTO #t (FieldDesc)
    VALUES    ('SQL SERVER CENTRAL'),
            ('JOHN DOE'),
            ('DICK VANDYKE'),
            ('OLIVIA DEHAVILLAND'),
            ('NILS VANDERSLUICE');
/*
-- LAST 3 ENTRIES SHOULD BE:
--    Dick VanDyke
--    Olivia DeHavilland
--    Nils VanDerSluice
*/
  I know that someone once posted some kind of Proper function on this site at one point, but I don't have time to go search for it.   It took a LOT of those kinds of things into account.

  I had one thread about a proper case function, but got deleted when all the threads with posts from a certain trolling user got deleted. However, there should be some available in this site and apparently one of the fastest is a scalar function and not an ITVF.

  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2

• jonathan.crawford

  SSCertifiable

  Points: 6613

  More actions

  May 2, 2018 at 6:14 am

  #1988912

  Jeff Moden - Initial creation and unit test (http://www.sqlservercentral.com/Forums/Topic530630-8-2.aspx)

  -------------------------------------------------------------------------------------------------------------------------------------
  Please follow Best Practices For Posting On Forums to receive quicker and higher quality responses

• Ed Wagner

  SSC Guru

  Points: 287026

  More actions

  May 2, 2018 at 6:50 am

  #1988920

  Luis Cazares - Tuesday, May 1, 2018 12:38 PM

  sgmunson - Tuesday, May 1, 2018 10:43 AM

  Sowbhari - Tuesday, May 1, 2018 9:37 AM

  rjjh78 - Tuesday, May 1, 2018 9:14 AM

  Hello,
  I have a field that contains all capital letters.  For example, "SQL SERVER CENTRAL". 
  Is there a way I can update the field to read, "Sql Server Central"?

  
CREATE TABLE #t (ID int IDENTITY(1,1), FieldDesc varchar(25))
INSERT INTO #t (FieldDesc) VALUES ('SQL SERVER CENTRAL')
INSERT INTO #t (FieldDesc) VALUES ('JOHN DOE')

  SELECT ID, FieldDesc FROM #t
  

  -- Desired Results:
  -- 1 Sql Server Central
  -- 2 John Doe

  Many thanks in advance!
  

  Credit to ChisM@Work
  
UPDATE #T SET FieldDesc =
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
' ' + LOWER(FieldDesc) COLLATE LATIN1_GENERAL_BIN ,' z',' Z'),' y',' Y'),' x',' X'),' w',' W'),' v',' V'),' u',' U'),
' t',' T'),' s',' S'),' r',' R'),' q',' Q'),' p',' P'),' o',' O'),' n',' N'),' m',' M'),' l',' L'),' k',' K'),
' j',' J'),' i',' I'),' h',' H'),' g',' G'),' f',' F'),' e',' E'),' d',' D'),' c',' C'),' b',' B'),' a',' A')


  And what do you plan to do for entries such as CREATE TABLE #t (
    ID int IDENTITY(1,1),
    FieldDesc varchar(25)
);
INSERT INTO #t (FieldDesc)
    VALUES    ('SQL SERVER CENTRAL'),
            ('JOHN DOE'),
            ('DICK VANDYKE'),
            ('OLIVIA DEHAVILLAND'),
            ('NILS VANDERSLUICE');
/*
-- LAST 3 ENTRIES SHOULD BE:
--    Dick VanDyke
--    Olivia DeHavilland
--    Nils VanDerSluice
*/
  I know that someone once posted some kind of Proper function on this site at one point, but I don't have time to go search for it.   It took a LOT of those kinds of things into account.

  I had one thread about a proper case function, but got deleted when all the threads with posts from a certain trolling user got deleted. However, there should be some available in this site and apparently one of the fastest is a scalar function and not an ITVF.

  I remember that thread - Sean Lange posted the original topic and I saved it in my briefcase because the discussion and testing was so very good.  You're correct in that the whole thing was deleted.  I didn't know it got caught up in the troll posts.

  Tally Tables - Performance Personified
  String Splitting with True Performance
  Best practices on how to ask questions

• Sean Lange

  SSC Guru

  Points: 286590

  More actions

  May 2, 2018 at 7:20 am

  #1988924

  Ed Wagner - Wednesday, May 2, 2018 6:50 AM

  Luis Cazares - Tuesday, May 1, 2018 12:38 PM

  sgmunson - Tuesday, May 1, 2018 10:43 AM

  Sowbhari - Tuesday, May 1, 2018 9:37 AM

  rjjh78 - Tuesday, May 1, 2018 9:14 AM

  Hello,
  I have a field that contains all capital letters.  For example, "SQL SERVER CENTRAL". 
  Is there a way I can update the field to read, "Sql Server Central"?

  
CREATE TABLE #t (ID int IDENTITY(1,1), FieldDesc varchar(25))
INSERT INTO #t (FieldDesc) VALUES ('SQL SERVER CENTRAL')
INSERT INTO #t (FieldDesc) VALUES ('JOHN DOE')

  SELECT ID, FieldDesc FROM #t
  

  -- Desired Results:
  -- 1 Sql Server Central
  -- 2 John Doe

  Many thanks in advance!
  

  Credit to ChisM@Work
  
UPDATE #T SET FieldDesc =
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
' ' + LOWER(FieldDesc) COLLATE LATIN1_GENERAL_BIN ,' z',' Z'),' y',' Y'),' x',' X'),' w',' W'),' v',' V'),' u',' U'),
' t',' T'),' s',' S'),' r',' R'),' q',' Q'),' p',' P'),' o',' O'),' n',' N'),' m',' M'),' l',' L'),' k',' K'),
' j',' J'),' i',' I'),' h',' H'),' g',' G'),' f',' F'),' e',' E'),' d',' D'),' c',' C'),' b',' B'),' a',' A')


  And what do you plan to do for entries such as CREATE TABLE #t (
    ID int IDENTITY(1,1),
    FieldDesc varchar(25)
);
INSERT INTO #t (FieldDesc)
    VALUES    ('SQL SERVER CENTRAL'),
            ('JOHN DOE'),
            ('DICK VANDYKE'),
            ('OLIVIA DEHAVILLAND'),
            ('NILS VANDERSLUICE');
/*
-- LAST 3 ENTRIES SHOULD BE:
--    Dick VanDyke
--    Olivia DeHavilland
--    Nils VanDerSluice
*/
  I know that someone once posted some kind of Proper function on this site at one point, but I don't have time to go search for it.   It took a LOT of those kinds of things into account.

  I had one thread about a proper case function, but got deleted when all the threads with posts from a certain trolling user got deleted. However, there should be some available in this site and apparently one of the fastest is a scalar function and not an ITVF.

  I remember that thread - Sean Lange posted the original topic and I saved it in my briefcase because the discussion and testing was so very good.  You're correct in that the whole thing was deleted.  I didn't know it got caught up in the troll posts.

  hehe I don't remember this thread. 🙂 But I do remember several different discussions on this topic over the years.

  _______________________________________________________________

  Need help? Help us help you.

  Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

  Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

  Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
  Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
  Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
  Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

• Ed Wagner

  SSC Guru

  Points: 287026

  More actions

  May 2, 2018 at 7:33 am

  #1988926

  Sean Lange - Wednesday, May 2, 2018 7:20 AM

  Ed Wagner - Wednesday, May 2, 2018 6:50 AM

  Luis Cazares - Tuesday, May 1, 2018 12:38 PM

  sgmunson - Tuesday, May 1, 2018 10:43 AM

  Sowbhari - Tuesday, May 1, 2018 9:37 AM

  rjjh78 - Tuesday, May 1, 2018 9:14 AM

  Hello,
  I have a field that contains all capital letters.  For example, "SQL SERVER CENTRAL". 
  Is there a way I can update the field to read, "Sql Server Central"?

  
CREATE TABLE #t (ID int IDENTITY(1,1), FieldDesc varchar(25))
INSERT INTO #t (FieldDesc) VALUES ('SQL SERVER CENTRAL')
INSERT INTO #t (FieldDesc) VALUES ('JOHN DOE')

  SELECT ID, FieldDesc FROM #t
  

  -- Desired Results:
  -- 1 Sql Server Central
  -- 2 John Doe

  Many thanks in advance!
  

  Credit to ChisM@Work
  
UPDATE #T SET FieldDesc =
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
' ' + LOWER(FieldDesc) COLLATE LATIN1_GENERAL_BIN ,' z',' Z'),' y',' Y'),' x',' X'),' w',' W'),' v',' V'),' u',' U'),
' t',' T'),' s',' S'),' r',' R'),' q',' Q'),' p',' P'),' o',' O'),' n',' N'),' m',' M'),' l',' L'),' k',' K'),
' j',' J'),' i',' I'),' h',' H'),' g',' G'),' f',' F'),' e',' E'),' d',' D'),' c',' C'),' b',' B'),' a',' A')


  And what do you plan to do for entries such as CREATE TABLE #t (
    ID int IDENTITY(1,1),
    FieldDesc varchar(25)
);
INSERT INTO #t (FieldDesc)
    VALUES    ('SQL SERVER CENTRAL'),
            ('JOHN DOE'),
            ('DICK VANDYKE'),
            ('OLIVIA DEHAVILLAND'),
            ('NILS VANDERSLUICE');
/*
-- LAST 3 ENTRIES SHOULD BE:
--    Dick VanDyke
--    Olivia DeHavilland
--    Nils VanDerSluice
*/
  I know that someone once posted some kind of Proper function on this site at one point, but I don't have time to go search for it.   It took a LOT of those kinds of things into account.

  I had one thread about a proper case function, but got deleted when all the threads with posts from a certain trolling user got deleted. However, there should be some available in this site and apparently one of the fastest is a scalar function and not an ITVF.

  I remember that thread - Sean Lange posted the original topic and I saved it in my briefcase because the discussion and testing was so very good.  You're correct in that the whole thing was deleted.  I didn't know it got caught up in the troll posts.

  hehe I don't remember this thread. 🙂 But I do remember several different discussions on this topic over the years.

  It was a great thread.  You were working on a system based on a system that shall not be named. 😉  Your post (titled ProperCase Function) asked the question about the most efficient ways people have done it.  What followed was some great discussion and testing by a lot of people.  I remember trying and working on many variants of it myself and it was a really cool exercise.  In the end, the results were unexpected - the SF beat the ITVF.  The URL to the post is http://www.sqlservercentral.com/Forums/FindPost1531616.aspx, but is dead.

  The timing of it was around late summer or early fall of 2016.  I remember this so clearly because I was working on a presentation on the different types of functions as a part of the SQL Saturday Pre-Con that Jeff and I did in Pittsburgh at the end of September. The innovative SF solution floored me.

  Tally Tables - Performance Personified
  String Splitting with True Performance
  Best practices on how to ask questions

• Jeff Moden

  SSC Guru

  Points: 1004405

  More actions

  May 2, 2018 at 7:37 am

  #1988928

  jonathan.crawford - Wednesday, May 2, 2018 6:14 AM

  Jeff Moden - Initial creation and unit test (http://www.sqlservercentral.com/Forums/Topic530630-8-2.aspx)

  Wow... that was a while back.  I'm humbled that you kept a link to it.  It also reminded me that I was going to try something a bit different so that it would work in an iTVF using a reverse order Tally table.  I guess I never got back to it because I never needed to use such a thing.

  I also like the Nested Replaces that Chris did alot.  Should be nasty fast especially with the binary collation that was used.

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

• Jason A. Long

  SSC-Insane

  Points: 23786

  More actions

  May 2, 2018 at 10:37 am

  #1988961

  Here's an option that should work fairly well. 
  SET QUOTED_IDENTIFIER ON;
GO
SET ANSI_NULLS ON;
GO

  CREATE FUNCTION dbo.tfn_ProperCase
/* ===================================================================
05/02/2018 JL, Created:
                This function will capitalize the first letter
                in a string plus any alpha character that follows a
                non alpha character or apostrophe
=================================================================== */
--===== Define I/O parameters
(
    @_string VARCHAR(8000)
)
RETURNS TABLE WITH SCHEMABINDING AS
RETURN
    WITH
    cte_n1 (n) AS (SELECT 1 FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) n (n)),
    cte_n2 (n) AS (SELECT 1 FROM cte_n1 a CROSS JOIN cte_n1 b),
    cte_Tally (n) AS (
        SELECT TOP (LEN(@_string))
            ROW_NUMBER() OVER (ORDER BY b.n)
        FROM
            cte_n2 a CROSS JOIN cte_n2 b
        )
SELECT
    CasedString = ((
        SELECT
            CONCAT('', cv.cased_value)
        FROM
            cte_Tally t
            CROSS APPLY ( VALUES (
                    CASE
                        WHEN SUBSTRING(@_string, t.n - 1, 1) NOT LIKE '[''a-Z]'
                        THEN UPPER(SUBSTRING(@_string, t.n, 1))
                        ELSE LOWER(SUBSTRING(@_string, t.n, 1))
                    END
                ) ) cv (cased_value)
        FOR XML PATH(''), TYPE).value('.', 'varchar(8000)')
        );
GO

  Usage:
  SELECT
    pc.CasedString
FROM
    dbo.tfn_ProperCase('THIS IS just a test. HERE''S WHAT I''M working with.') pc;

  Output:
  

  This Is Just A Test. Here's What I'm Working With.

  Side note: The use of FOR XML PATH results in the "XML Reader" function showing up in the execution plan. According the the execution plan, this is accounting for 98% of the total cost of the function.
  Yes, the plan does lie about costs but I would still recommend (for anyone using SQL Server 2017 or later) using the STRING_AGG function instead.

• Ed Wagner

  SSC Guru

  Points: 287026

  More actions

  May 2, 2018 at 11:28 am

  #1988970

  Jason A. Long - Wednesday, May 2, 2018 10:37 AM

  Here's an option that should work fairly well. 
  SET QUOTED_IDENTIFIER ON;
  GO
  SET ANSI_NULLS ON;
  GO

  CREATE FUNCTION dbo.tfn_ProperCase
/* ===================================================================
05/02/2018 JL, Created:
                This function will capitolize the first letter
                in a string plusr any alpha character that follows a
                non alpha character or apostrophe
=================================================================== */
--===== Define I/O parameters
(
    @_string VARCHAR(8000)
)
RETURNS TABLE WITH SCHEMABINDING AS
RETURN
    WITH
    cte_n1 (n) AS (SELECT 1 FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) n (n)),
    cte_n2 (n) AS (SELECT 1 FROM cte_n1 a CROSS JOIN cte_n1 b),
    cte_Tally (n) AS (
        SELECT TOP (LEN(@_string))
            ROW_NUMBER() OVER (ORDER BY b.n)
        FROM
            cte_n2 a CROSS JOIN cte_n2 b
        )
SELECT
    CasedString = ((
        SELECT
            CONCAT('', cv.cased_value)
        FROM
            cte_Tally t
            CROSS APPLY ( VALUES (
                    CASE
                        WHEN SUBSTRING(@_string, t.n - 1, 1) NOT LIKE '[''a-Z]'
                        THEN UPPER(SUBSTRING(@_string, t.n, 1))
                        ELSE LOWER(SUBSTRING(@_string, t.n, 1))
                    END
                ) ) cv (cased_value)
        FOR XML PATH(''), TYPE).value('.', 'varchar(8000)')
        );
GO

  Usage:
  SELECT
    pc.CasedString
FROM
    dbo.tfn_ProperCase('THIS IS just a test. HERE''S WHAT I''M working with.') pc;

  Output:
  

  This Is Just A Test. Here's What I'm Working With.

  Side note: The use of FOR XML PATH results in the "XML Reader" function showing up in the execution plan. According the the execution plan, this is accounting for 98% of the total cost of the function.
  Yes, the plan does lie about costs but I would still recommend (for anyone using SQL Server 2017 or later) using the STRING_AGG function instead.

  Nice.  I don't have time to put it through a real test right now, but I'd estimate the de-entitization consumes about 3/4 of the total time.  I know it's needed, but wish it performed better.

  Tally Tables - Performance Personified
  String Splitting with True Performance
  Best practices on how to ask questions

• Jason A. Long

  SSC-Insane

  Points: 23786

  More actions

  May 2, 2018 at 11:40 am

  #1988972

  Ed Wagner - Wednesday, May 2, 2018 11:28 AM

  Nice.  I don't have time to put it through a real test right now, but I'd estimate the de-entitization consumes about 3/4 of the total time.  I know it's needed, but wish it performed better.

  Yea... You and me both...
  The only other option (off the top of my head) would be to use a recursive CTE. Even with the de-entitization, this approach should still blow a recursive cte out of the water... So, in 2012, I'm not seeing a better option.
  That said, I'll be interested to see what else get proposed.

• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  May 2, 2018 at 11:44 am

  #1988974

  Ed Wagner - Wednesday, May 2, 2018 11:28 AM

  Jason A. Long - Wednesday, May 2, 2018 10:37 AM

  Here's an option that should work fairly well. 
  SET QUOTED_IDENTIFIER ON;
  GO
  SET ANSI_NULLS ON;
  GO

  CREATE FUNCTION dbo.tfn_ProperCase
/* ===================================================================
05/02/2018 JL, Created:
                This function will capitolize the first letter
                in a string plusr any alpha character that follows a
                non alpha character or apostrophe
=================================================================== */
--===== Define I/O parameters
(
    @_string VARCHAR(8000)
)
RETURNS TABLE WITH SCHEMABINDING AS
RETURN
    WITH
    cte_n1 (n) AS (SELECT 1 FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) n (n)),
    cte_n2 (n) AS (SELECT 1 FROM cte_n1 a CROSS JOIN cte_n1 b),
    cte_Tally (n) AS (
        SELECT TOP (LEN(@_string))
            ROW_NUMBER() OVER (ORDER BY b.n)
        FROM
            cte_n2 a CROSS JOIN cte_n2 b
        )
SELECT
    CasedString = ((
        SELECT
            CONCAT('', cv.cased_value)
        FROM
            cte_Tally t
            CROSS APPLY ( VALUES (
                    CASE
                        WHEN SUBSTRING(@_string, t.n - 1, 1) NOT LIKE '[''a-Z]'
                        THEN UPPER(SUBSTRING(@_string, t.n, 1))
                        ELSE LOWER(SUBSTRING(@_string, t.n, 1))
                    END
                ) ) cv (cased_value)
        FOR XML PATH(''), TYPE).value('./text()[1]', 'varchar(8000)')
        );
GO

  Usage:
  SELECT
    pc.CasedString
FROM
    dbo.tfn_ProperCase('THIS IS just a test. HERE''S WHAT I''M working with.') pc;

  Output:
  

  This Is Just A Test. Here's What I'm Working With.

  Side note: The use of FOR XML PATH results in the "XML Reader" function showing up in the execution plan. According the the execution plan, this is accounting for 98% of the total cost of the function.
  Yes, the plan does lie about costs but I would still recommend (for anyone using SQL Server 2017 or later) using the STRING_AGG function instead.

  Nice.  I don't have time to put it through a real test right now, but I'd estimate the de-entitization consumes about 3/4 of the total time.  I know it's needed, but wish it performed better.

  Have you tried with the small change that I added to the code? It should offer a noticeable performance improvement. Although, from the deleted thread I remember we tried several things and the scalar function was still faster. I wonder what would happen when using STRING_AGG

  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2

• Jason A. Long

  SSC-Insane

  Points: 23786

  More actions

  May 2, 2018 at 2:19 pm

  #1988991

  Luis Cazares - Wednesday, May 2, 2018 11:44 AM

  Ed Wagner - Wednesday, May 2, 2018 11:28 AM

  Jason A. Long - Wednesday, May 2, 2018 10:37 AM

  Here's an option that should work fairly well. 
  SET QUOTED_IDENTIFIER ON;
  GO
  SET ANSI_NULLS ON;
  GO

  CREATE FUNCTION dbo.tfn_ProperCase
/* ===================================================================
05/02/2018 JL, Created:
                This function will capitolize the first letter
                in a string plusr any alpha character that follows a
                non alpha character or apostrophe
=================================================================== */
--===== Define I/O parameters
(
    @_string VARCHAR(8000)
)
RETURNS TABLE WITH SCHEMABINDING AS
RETURN
    WITH
    cte_n1 (n) AS (SELECT 1 FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) n (n)),
    cte_n2 (n) AS (SELECT 1 FROM cte_n1 a CROSS JOIN cte_n1 b),
    cte_Tally (n) AS (
        SELECT TOP (LEN(@_string))
            ROW_NUMBER() OVER (ORDER BY b.n)
        FROM
            cte_n2 a CROSS JOIN cte_n2 b
        )
SELECT
    CasedString = ((
        SELECT
            CONCAT('', cv.cased_value)
        FROM
            cte_Tally t
            CROSS APPLY ( VALUES (
                    CASE
                        WHEN SUBSTRING(@_string, t.n - 1, 1) NOT LIKE '[''a-Z]'
                        THEN UPPER(SUBSTRING(@_string, t.n, 1))
                        ELSE LOWER(SUBSTRING(@_string, t.n, 1))
                    END
                ) ) cv (cased_value)
        FOR XML PATH(''), TYPE).value('./text()[1]', 'varchar(8000)')
        );
GO

  Usage:
  SELECT
    pc.CasedString
FROM
    dbo.tfn_ProperCase('THIS IS just a test. HERE''S WHAT I''M working with.') pc;

  Output:
  

  This Is Just A Test. Here's What I'm Working With.

  Side note: The use of FOR XML PATH results in the "XML Reader" function showing up in the execution plan. According the the execution plan, this is accounting for 98% of the total cost of the function.
  Yes, the plan does lie about costs but I would still recommend (for anyone using SQL Server 2017 or later) using the STRING_AGG function instead.

  Nice.  I don't have time to put it through a real test right now, but I'd estimate the de-entitization consumes about 3/4 of the total time.  I know it's needed, but wish it performed better.

  Have you tried with the small change that I added to the code? It should offer a noticeable performance improvement. Although, from the deleted thread I remember we tried several things and the scalar function was still faster. I wonder what would happen when using STRING_AGG

  VERY COOL LUIS!

  I had no idea that changing that value would make that big of a difference the execution plan.

  First, I'll start with an apology... I was lazy and tested with company data so I cannot share it here...
  As a quick test I dropped 10,000 comments into a temp table to compare.
  The obvious difference is that the "LC" version eliminates the need for a work table and reduces the estimated cost of the XML Reader from 9567.56 down to 50.2...

  Unfortunately, the actual/experienced differences aren't quite as dramatic as the execution plans would have us believe...
  

    wait a moment...
  â•”â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•—
  â•‘    Start Time: 2018-05-02 16:07:15.3178370   Test Name: tfn_ProperCase_JL                     â•‘
  â•šâ•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•
  Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
  Table '#TestData___________________________________________________________________________________________________________00000000139D'. Scan count 1, logical reads 213, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
  â•”â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•—
  â•‘    Finish Time: 2018-05-02 16:07:16.6388710   Duration: 1.321034 secs.  1321.034000 ms.                â•‘
  â•šâ•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•

     wait a moment...
  â•”â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•—                              
  â•‘    Start Time: 2018-05-02 16:07:17.8689626   Test Name: tfn_ProperCase_LC                     â•‘
  â•šâ•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•
  Table '#TestData___________________________________________________________________________________________________________00000000139D'. Scan count 1, logical reads 213, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
  â•”â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•—
  â•‘    Finish Time: 2018-05-02 16:07:19.2550269   Duration: 1.386064 secs.  1386.064000 ms.                â•‘
  â•šâ•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•

   

    wait a moment...
  â•”â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•—
  â•‘    Start Time: 2018-05-02 16:08:51.5573238   Test Name: tfn_ProperCase_JL                     â•‘
  â•šâ•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•
  Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
  Table '#TestData___________________________________________________________________________________________________________00000000139D'. Scan count 1, logical reads 213, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
  â•”â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•—
  â•‘    Finish Time: 2018-05-02 16:08:52.8823824   Duration: 1.325059 secs.  1325.059000 ms.                â•‘
  â•šâ•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•

     wait a moment...
  â•”â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•—                              
  â•‘    Start Time: 2018-05-02 16:08:54.1014646   Test Name: tfn_ProperCase_LC                     â•‘
  â•šâ•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•
  Table '#TestData___________________________________________________________________________________________________________00000000139D'. Scan count 1, logical reads 213, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
  â•”â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•—
  â•‘    Finish Time: 2018-05-02 16:08:55.3605241   Duration: 1.259060 secs.  1259.060000 ms.                â•‘
  â•šâ•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•

  On the whole, the LC version tended to be slightly faster than the original but not anything close to what the differences in estimated plan costs would imply.

  When I get home this evening , I'll create a better test harness, that can be shared, so that everyone can play along.

• Jeff Moden

  SSC Guru

  Points: 1004405

  More actions

  May 2, 2018 at 4:47 pm

  #1989007

  I'm seriously missing it in the thread above.  What "small change" did Luis make?

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