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: 997150

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



    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


    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.






    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.
    "Change is inevitable... change for the better is not".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

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