Changing Text Case?


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 3 of 3

Thread: Changing Text Case?

  1. #1
    SQL Guest

    Changing Text Case?


    How would I change the case of a value in my table? Currently the values are
    all upper case. In siautions like names I would like to keep the first letter
    uppers case and the remainder in lower case...as appropriate. What T-SQL
    statements help with this type of work.

    Thanks.

  2. #2
    David Satz Guest

    Re: Changing Text Case?

    /***************************************************************************
    **********************
    Copyright 2001 Narayana Vyas Kondreddi. All rights reserved.

    Purpose: To convert a given string to proper case

    Written by: Narayana Vyas Kondreddi
    http://vyaskn.tripod.com

    Tested on: SQL Server 2000

    Date modified: May-7-2001 06:44 PM

    Email: vyaskn@hotmail.com

    Examples:

    To convert the string 'william h gates' to proper case:
    SELECT dbo.propercase('william h gates')

    To convert the Notes field of titles table in pubs database to proper case:
    SELECT dbo.propercase(notes) FROM pubs..titles
    ****************************************************************************
    *********************/
    if exists (select * from dbo.sysobjects where id =
    object_id(N'[dbo].[propercase]') and xtype in (N'FN', N'IF', N'TF'))
    drop function [dbo].[propercase]
    GO

    CREATE FUNCTION dbo.propercase
    (
    --The string to be converted to proper case
    @input varchar(8000)
    )
    --This function returns the proper case string of varchar type
    RETURNS varchar(8000)
    AS
    BEGIN
    IF @input IS NULL
    BEGIN
    --Just return NULL if input string is NULL
    RETURN NULL
    END

    --Character variable declarations
    DECLARE @output varchar(8000)
    --Integer variable declarations
    DECLARE @ctr int, @len int, @found_at int
    --Constant declarations
    DECLARE @LOWER_CASE_a int, @LOWER_CASE_z int, @WHITE_SPACE char

    --Variable/Constant initializations
    SET @ctr = 1
    SET @len = LEN(@input)
    SET @output = ''
    SET @LOWER_CASE_a = 97
    SET @LOWER_CASE_z = 122
    SET @WHITE_SPACE = ' '

    WHILE @ctr <= @len
    BEGIN
    --This loop will take care of reccuring white spaces
    WHILE SUBSTRING(@input,@ctr,1) = @WHITE_SPACE
    BEGIN
    SET @output = @output + SUBSTRING(@input,@ctr,1)
    SET @ctr = @ctr + 1
    END

    IF ASCII(SUBSTRING(@input,@ctr,1)) BETWEEN @LOWER_CASE_a AND @LOWER_CASE_z
    BEGIN
    --Converting the first character to upper case
    SET @output = @output + UPPER(SUBSTRING(@input,@ctr,1))
    END
    ELSE
    BEGIN
    SET @output = @output + SUBSTRING(@input,@ctr,1)
    END

    SET @ctr = @ctr + 1

    --Jumping to the beginning of next word
    SET @found_at = CHARINDEX(@WHITE_SPACE,@input,@ctr)
    IF @found_at <> 0
    BEGIN
    SET @output = @output + SUBSTRING(@input,@ctr,@found_at - @ctr)
    SET @ctr = @found_at
    END
    ELSE
    BEGIN
    SET @output = @output + SUBSTRING(@input,@ctr,(@len-@ctr) + 1)
    SET @ctr = @len + 1
    END


    END
    RETURN @output
    END
    --
    HTH,
    David Satz
    Principal Web Engineer
    Hyperion Solutions


    "SQL" <SQL@SQL.com> wrote in message news:3ec8f555$1@tnews.web.devx.com...
    >
    > How would I change the case of a value in my table? Currently the values

    are
    > all upper case. In siautions like names I would like to keep the first

    letter
    > uppers case and the remainder in lower case...as appropriate. What T-SQL
    > statements help with this type of work.
    >
    > Thanks.




  3. #3
    mister pants Guest

    Re: Changing Text Case?


    Nice, but that doesn't help for names like McDonald or O'Brian

    Generally "Mc" and "O'" prefixes need some additional logic.

    BTW, Oracle has a nice INITCAP function, amongst others

    :-)




    "David Satz" <davidNOSPAMsatz@yahoo.NOSPAM.com> wrote:
    >/***************************************************************************
    >**********************
    >Copyright 2001 Narayana Vyas Kondreddi. All rights reserved.
    >
    >Purpose: To convert a given string to proper case
    >
    >Written by: Narayana Vyas Kondreddi
    > http://vyaskn.tripod.com
    >
    >Tested on: SQL Server 2000
    >
    >Date modified: May-7-2001 06:44 PM
    >
    >Email: vyaskn@hotmail.com
    >
    >Examples:
    >
    >To convert the string 'william h gates' to proper case:
    >SELECT dbo.propercase('william h gates')
    >
    >To convert the Notes field of titles table in pubs database to proper case:
    >SELECT dbo.propercase(notes) FROM pubs..titles
    >****************************************************************************
    >*********************/
    >if exists (select * from dbo.sysobjects where id =
    >object_id(N'[dbo].[propercase]') and xtype in (N'FN', N'IF', N'TF'))
    > drop function [dbo].[propercase]
    >GO
    >
    >CREATE FUNCTION dbo.propercase
    >(
    >--The string to be converted to proper case
    >@input varchar(8000)
    >)
    >--This function returns the proper case string of varchar type
    >RETURNS varchar(8000)
    >AS
    >BEGIN
    > IF @input IS NULL
    > BEGIN
    > --Just return NULL if input string is NULL
    > RETURN NULL
    > END
    >
    > --Character variable declarations
    > DECLARE @output varchar(8000)
    > --Integer variable declarations
    > DECLARE @ctr int, @len int, @found_at int
    > --Constant declarations
    > DECLARE @LOWER_CASE_a int, @LOWER_CASE_z int, @WHITE_SPACE char
    >
    > --Variable/Constant initializations
    > SET @ctr = 1
    > SET @len = LEN(@input)
    > SET @output = ''
    > SET @LOWER_CASE_a = 97
    > SET @LOWER_CASE_z = 122
    > SET @WHITE_SPACE = ' '
    >
    > WHILE @ctr <= @len
    > BEGIN
    > --This loop will take care of reccuring white spaces
    > WHILE SUBSTRING(@input,@ctr,1) = @WHITE_SPACE
    > BEGIN
    > SET @output = @output + SUBSTRING(@input,@ctr,1)
    > SET @ctr = @ctr + 1
    > END
    >
    > IF ASCII(SUBSTRING(@input,@ctr,1)) BETWEEN @LOWER_CASE_a AND @LOWER_CASE_z
    > BEGIN
    > --Converting the first character to upper case
    > SET @output = @output + UPPER(SUBSTRING(@input,@ctr,1))
    > END
    > ELSE
    > BEGIN
    > SET @output = @output + SUBSTRING(@input,@ctr,1)
    > END
    >
    > SET @ctr = @ctr + 1
    >
    > --Jumping to the beginning of next word
    > SET @found_at = CHARINDEX(@WHITE_SPACE,@input,@ctr)
    > IF @found_at <> 0
    > BEGIN
    > SET @output = @output + SUBSTRING(@input,@ctr,@found_at - @ctr)
    > SET @ctr = @found_at
    > END
    > ELSE
    > BEGIN
    > SET @output = @output + SUBSTRING(@input,@ctr,(@len-@ctr) + 1)
    > SET @ctr = @len + 1
    > END
    >
    >
    > END
    >RETURN @output
    >END
    >--
    >HTH,
    >David Satz
    >Principal Web Engineer
    >Hyperion Solutions
    >
    >
    >"SQL" <SQL@SQL.com> wrote in message news:3ec8f555$1@tnews.web.devx.com...
    >>
    >> How would I change the case of a value in my table? Currently the values

    >are
    >> all upper case. In siautions like names I would like to keep the first

    >letter
    >> uppers case and the remainder in lower case...as appropriate. What T-SQL
    >> statements help with this type of work.
    >>
    >> Thanks.

    >
    >



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center