Convert data from all Upper Case to "Proper Case"

[jennmcwood]

I need to be able to convert data that is currently in all caps (Upper Case) to data that is in "Proper Case" - I can do it by bringing the data out into another application (such as Access) and running a query there, then exporting the data back into a sql database, but I would prefer as little data transfer as possible.

Example:

JOHN C.DOE becomes John C. Doe
SUSIE QUE becomes Susie Que

Thanks,

JW

 

[Gundapaneni]

you can do it by using LOWER(char_exp) or UPPER(char_exp).
Lower function will be usefull to convert all upper case letters to lower case letters, the similar way UPPER function also.

It may help to U, I think.

Madhu.

 

[Gundapaneni]

you can do it by using functions LOWER(char_exp) or UPPER(char_exp). LOWER function will be used to convert all upper case letters to lower case letters, UPPER case function also in similar way used.

It may be helpful to U, I think.

Madhu.

 

[bperry]

I saw this UDF womewhere on the web. It's supposed to assign proper case. Perhaps with adaptation it will work for you. (But you need SQL Server 2000 for UDFs; otherwise, re-work into a stored procedure)

CREATE FUNCTION TitleCase (
@DataIn VARCHAR(50)
)
RETURNS Varchar(50) AS
BEGIN
DECLARE @Store VARCHAR(50)
DECLARE @i INT
SET @Store = LOWER(@DataIn)
SET @Store = UPPER(LEFT(@Store,1)) + SUBSTRING(@Store,2,LEN(@Store))

WHILE CHARINDEX(' ',@Store,1) > 0
BEGIN
SET @i = CHARINDEX(' ',@Store,1)
SET @Store = LEFT(@Store,@i-1) + '~*' + UPPER(SUBSTRING(@store,@i + 1,1)) +SUBSTRING(@Store,@i+2,LEN(@Store))
END

SET @Store = REPLACE(@Store,'~*',' ')
SET @DataIn = @Store
RETURN @DataIn
END