Change word that r in upper like the Excel function proper

• jvElecenter

  SSCarpal Tunnel

  Points: 4502

  More actions

  March 17, 2004 at 9:10 am

  #163308

  Hi

  I'm looking for the function like there is in Excel : "proper" to change text from upper to neat.

   

  Like : HELLO to Hello

  or UNITED KINGDOM to United Kingdom

   

  Can anyone help me ?

  Think it's quit simple but don't find it and .... time ????

  Thx

  
  JV

• winash

  SSChampion

  Points: 11446

  More actions

  March 17, 2004 at 10:12 am

  #499163

  The scripts section of this site have lots of stuff for such situations...here are two scripts that do the "neat" casing...(in the T-SQL Aids scripts section)

  http://qa.sqlservercentral.com/scripts/contributions/981.asp

  http://qa.sqlservercentral.com/scripts/contributions/890.asp

• Angela But

  Hall of Fame

  Points: 3848

  More actions

  March 21, 2004 at 9:19 pm

  #499693

  Or you can use this function, which I did post in the Scripts section, but since the website upgrade, for one reason or another it no longer comes up in a search.

   

  /*This function will evaluate an input string and convert it to title-case format.

  it uses the delimchars parameter to determine what are the triggering characters

   to indicate capitalization of the subsequent character*/

  CREATE FUNCTION  fnTitleCase (@instring nvarchar(256))

  RETURNS nvarchar(256)

  AS

  BEGIN

    DECLARE @strptr INT

    DECLARE @outstring nvarchar(255)

    DECLARE @strChar char(1)

    DECLARE @delimchars nvarchar(256)

    SET @outstring = ''

    SET @strptr = 0

    IF @delimchars is NULL

      /* Plug in typical upper-case delimiters

   NOTE: For localization purposes, you may wish to modify this */

      SET @delimchars = ' ''-_.,'

    /* Loop through the entire string */

    WHILE @strPtr < len(RTRIM(@instring))

      BEGIN

        SET @strptr = @strptr + 1

        SET @strchar = SUBSTRING(@instring,@strptr,1)

        IF @strptr = 1

   /* Assume that first character must always being upper-cased*/

          SET @outstring = UPPER(@strchar)

        ELSE

  /*Check for other upper-case trigger character */

          IF CHARINDEX(SUBSTRING(@instring,(@strptr - 1),1),@delimchars) > 0

  --          SET @outstring = SUBSTRING(@outstring,1,@strptr)+UPPER(@strchar)

            SET @outstring = @outstring+UPPER(@strchar)

          ELSE

            SET @outstring = @outstring+LOWER(@strchar)

      END

     RETURN @outstring

  END

   

  Cheers,

  Angela