Changing Text Case?

[SQL]

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.

[David Satz]

/***************************************************************************
**********************
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.

[mister pants]

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