Home Forums SQL Server 7,2000 T-SQL First and first character after space should be capital. RE: First and first character after space should be capital.

  • Jeff Moden

    SSC Guru

    Points: 994279

    I know this is an older post but, if you're interested in performance, here's a new method I ran across. Yeah... I know. The RBAR method is actually faster than the Tally Table method because THIS RBAR method is all in memory. As good as it is, even a cached Tally Table can't quite keep up.

    Almost forgot... here's the code I ran across. Kudos to George Mastros for the original concept.

    CREATE FUNCTION dbo.InitialCap(@String VARCHAR(8000))

    /***************************************************************************************************

    Purpose:

    Capitalize any lower case alpha character which follows any non alpha character or single quote.

    Revision History:

    Rev 00 - 24 Feb 2010 - George Mastros - Initial concept

    http://blogs.lessthandot.com/index.php/DataMgmt/DBProgramming/sql-server-proper-case-function

    Rev 01 - 25 Sep 2010 - Jeff Moden

    - Redaction for personal use and added documentation.

    - Slight speed enhancement by adding additional COLLATE clauses that shouldn't have mattered

    - and the reduction of multiple SET statements to just 2 SELECT statements.

    - Add no-cap single-quote by single-quote to the filter.

    ***************************************************************************************************/

    RETURNS VARCHAR(8000)

    AS

    BEGIN

    ----------------------------------------------------------------------------------------------------

    DECLARE @Position INT

    ;

    --===== Update the first character no matter what and then find the next postion that we

    -- need to update. The collation here is essential to making this so simple.

    -- A-z is equivalent to the slower A-Z

    SELECT @String = STUFF(LOWER(@String),1,1,UPPER(LEFT(@String,1))) COLLATE Latin1_General_Bin,

    @Position = PATINDEX('%[^A-Za-z''][a-z]%',@String COLLATE Latin1_General_Bin)

    ;

    --===== Do the same thing over and over until we run out of places to capitalize.

    -- Note the reason for the speed here is that ONLY places that need capitalization

    -- are even considered for @Position using the speed of PATINDEX.

    WHILE @Position > 0

    SELECT @String = STUFF(@String,@Position,2,UPPER(SUBSTRING(@String,@Position,2))) COLLATE Latin1_General_Bin,

    @Position = PATINDEX('%[^A-Za-z''][a-z]%',@String COLLATE Latin1_General_Bin)

    ;

    ----------------------------------------------------------------------------------------------------

    RETURN @String;

    END ;

    --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
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)