Post reply

How to Convert Case to Proper

  • December 2, 2007 at 8:53 pm

    #76833

    Hi All

    I have table with the column as Name in which I am storing the Name of the customer. Is there is anyway to convert the content as follows

    present To be converted as follows

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

    New york New York

    GRORGE BUSH George Bush

    india India

    a.k.menan A.K.Menan

    I want the Fisrt letter of every word should be in capital

  • December 3, 2007 at 12:14 am

    #756369

    What do you want to do with macintosh or mcintonsh?

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

  • December 3, 2007 at 12:43 am

    #756371

    Whilst we wait...

    CREATE FUNCTION dbo.ProperCase(@pString VARCHAR(8000))

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

    Purpose:

    This function will change all letters to lower case and then change the first letter that follows any non-alpha

    character to an upper case letter. In other words, it capitalizes the first letter of each word.

    Notes:

    1. Preserves spacing... no spaces/special characters are removed.

    2. Runs about twice as fast as a "Tally" or "Numbers" table solution on smaller sets of data and still runs faster

    (although not twice as fast) on larger sets. The reason for this is that it's a 100% memory solution and does not

    have the overhead of reading an index (even if the "Tally" table is pinned to memory).

    3. Uses the same number of I/O resources as the "Tally" table solution but fewer CPU resources.

    4. The delimiter for words in this code is any non-alpha character

    5. I was shocked at how much faster this is than the "Tally" table solution. Even old dogs like me can learn new

    things ;-)

    Revision History:

    Rev 00 - 02/17/2007 - Jeff Moden - Initial creation

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

    RETURNS VARCHAR(8000) AS

    BEGIN

    --===== Declare local variables

    DECLARE @Result VARCHAR(8000)

    SET @Result = ''

    --===== Set the whole string to lower case

    SELECT @pString = LOWER(@pString)

    --===== Loop through the words and change the first letter of each to upper case

    WHILE PATINDEX('%[^A-Z]%',@pString) > 0

    BEGIN

    --===== Change the first character of a word to upper case and add the word + trailing space to the result...

    SELECT @Result = @Result + UPPER(LEFT(@pString,1)) + SUBSTRING(@pString,2,PATINDEX('%[^A-Z]%',@pString)-1),

    -- and remove the word and trailing space we just added from the input string parameter

    @pString = SUBSTRING(@pString,PATINDEX('%[^A-Z]%',@pString)+1,LEN(@pString))

    END

    --===== Finally, change the first character of the last word to upper case and add it to the result for the return

    RETURN (@Result + UPPER(LEFT(@pString,1)) + SUBSTRING(@pString,2,LEN(@pString)))

    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.

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

  • December 5, 2007 at 9:06 pm

    #757503

    It Works Nice Many Thanks

    S.Saravana Kumar

  • December 5, 2007 at 10:04 pm

    #757507

    You bet... thank you for the feedback.

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

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply