none
Upper case and Lower Case RRS feed

  • Question

  • Hello Friends

    How do I convert first letter of each name to upper case and rest to lower case?  As an example,  JOHN MAXWELL should read as John Maxwell.

    In oracle there is a function called INITCAP, how do I do this in SQL Server?

    Your help is highly appreciated.

    Thanks in advance.



    Mohamed_Rafi

    Tuesday, June 11, 2019 3:30 PM

Answers

  • Hi Mohamed,

    You can use the below query to change the first letter in upper case and remaining are lower case

    UPDATE [yourtable]
    SET word=UPPER(LEFT(word,1))+LOWER(SUBSTRING(word,2,LEN(word)))

    if you want to view as a result, then you can use the below query

    SELECT UPPER(LEFT(word,1))+LOWER(SUBSTRING(word,2,LEN(word))) FROM [yourtable]

    Ref: https://stackoverflow.com/questions/15290754/sql-capitalize-first-letter-only

    _______________________ 

    Thivagar SEGAR
    [If this Post helps you, then please don't forget to mark it answer or Vote for this post]

    • Marked as answer by Mohamed_Rafi Tuesday, June 11, 2019 3:46 PM
    Tuesday, June 11, 2019 3:39 PM
  • Thanks Thivagar.   It works.  

    Mohamed_Rafi

    • Marked as answer by Mohamed_Rafi Tuesday, June 11, 2019 3:46 PM
    Tuesday, June 11, 2019 3:46 PM

All replies

  • Hi Mohamed,

    You can use the below query to change the first letter in upper case and remaining are lower case

    UPDATE [yourtable]
    SET word=UPPER(LEFT(word,1))+LOWER(SUBSTRING(word,2,LEN(word)))

    if you want to view as a result, then you can use the below query

    SELECT UPPER(LEFT(word,1))+LOWER(SUBSTRING(word,2,LEN(word))) FROM [yourtable]

    Ref: https://stackoverflow.com/questions/15290754/sql-capitalize-first-letter-only

    _______________________ 

    Thivagar SEGAR
    [If this Post helps you, then please don't forget to mark it answer or Vote for this post]

    • Marked as answer by Mohamed_Rafi Tuesday, June 11, 2019 3:46 PM
    Tuesday, June 11, 2019 3:39 PM
  • Thanks Thivagar.   It works.  

    Mohamed_Rafi

    • Marked as answer by Mohamed_Rafi Tuesday, June 11, 2019 3:46 PM
    Tuesday, June 11, 2019 3:46 PM
  • Hello Friends

    How do I convert first letter of each name to upper case and rest to lower case?  As an example,  JOHN MAXWELL should read as John Maxwell.

    In oracle there is a function called INITCAP, how do I do this in SQL Server?

    Your help is highly appreciated.

    Thanks in advance.



    Mohamed_Rafi

    Hello, Mohamed -

    This function will give you the proper case result of any input string. So, "this is a test" would return "This Is A Test."

    --------------------------------------------------------------------------------
    /*
       Convert a string to proper/title case.
    */
    CREATE FUNCTION dbo.ProperCase(@InputString varchar(4000))
    RETURNS varchar(4000)
    AS
       BEGIN
          DECLARE @Index int
          DECLARE @Char char(1)
          DECLARE @OutputString varchar(255)
             SET @OutputString = LOWER(@InputString)
             SET @Index = 2
             SET @OutputString
                = STUFF(@OutputString, 1, 1,UPPER(SUBSTRING(@InputString,1,1)))
          WHILE @Index <= LEN(@InputString)
             BEGIN
                SET @Char = SUBSTRING(@InputString, @Index, 1)
                IF @Char
                   IN
                      (
                      ' ', ';', ':', '!', '?', ',', '.', '_', '-', '/', '&',
                      '''','('
                      )
                IF @Index + 1 <= LEN(@InputString)
                   BEGIN
                      IF @Char != ''''
                      OR
                      UPPER(SUBSTRING(@InputString, @Index + 1, 1)) != 'S'
                      SET @OutputString
                         = STUFF(@OutputString, @Index + 1, 1,UPPER
                            (
                            SUBSTRING(@InputString, @Index + 1, 1))
                            )
                   END
                SET @Index = @Index + 1
             END
          RETURN ISNULL(@OutputString,'')
       END;
    --------------------------------------------------------------------------------

    Hope this helps.

    Tuesday, June 11, 2019 4:15 PM