Mixed case in sql 2

[Deam]

Hi, I need to convert data in a table, firstname, middlename, lastname address etc to mixed case. eg:If the first name = SAM ADAMS then it should be Sam Adams. If the Address is APT# 2345 then it should be Apt# 2345.

I can't seem to find the sql function. How should I do it ?

Thanks in advance.

 

[ecobb]

Here is a function that I found somewhere on this site, though it was so long ago I have no idea where I found it or who wrote it (my appologies to whomever it belongs to).



-- Create a function in a database.
-- This function will set a specified value in "Title Case", having the first letter capitalized, and all others lower case.

/* To use this function
Select dbo.Capitalize(lower('MUHAMMAD ESSA MUGHAL'), 'word')
If you want to update existing data then use like this
Update Customers
Set LastName = dbo.Capitalize(lower(LastName), 'word')
*/

/*To capitalize or lowercase all words in a table column, use this:
update Employees set Username = lower(username)
update Employees set Username = upper(username)
*/

CREATE FUNCTION dbo.Capitalize (
@String VARCHAR(8000),
@Capitalize_What VARCHAR(8) = 'string'
-- string: Capitalize the first letter
-- of the string
-- sentence: Capitalize the first letter
-- of every sentence.
-- Delimiters: ./!/?
-- word: Capitalize the first letter
-- of every word.
-- Delimiters: any characters
-- other than letters and digits.
)
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @Position SMALLINT,
@Char CHAR(1),
@First_Char CHAR(1),
@Word_Start SMALLINT

SET @Capitalize_What = LOWER( @Capitalize_What )
SET @Word_Start = 0
IF @Capitalize_What IN ( 'word', 'sentence' )
BEGIN
SET @Position = DATALENGTH( @String )
WHILE @Position >= 0 BEGIN
SET @Char = CASE @Position
WHEN 0 THEN '.'
ELSE UPPER( SUBSTRING(
@String, @Position, 1 ) )
END
IF @Char BETWEEN 'A' AND 'Z'
OR @Char BETWEEN '0' and '9' BEGIN
SET @Word_Start = @Position
SET @First_Char = UPPER( @Char )
END
ELSE BEGIN
IF @Capitalize_What = 'word'
OR @Char in ( '.', '!', '?' ) BEGIN
IF @Word_Start > 0
AND @First_Char BETWEEN 'A' AND 'Z'
SET @String = STUFF(@String, @Word_Start,
1, @First_Char )
SET @Word_Start = 0
END
END
SET @Position = @Position - 1
END
END
ELSE BEGIN -- Capitalize the first character
SET @Position = 0
WHILE @Position < DATALENGTH( @String )
BEGIN
SET @Position = @Position + 1
SET @Char = UPPER( SUBSTRING( @String,
@Position, 1 ) )
IF @Char BETWEEN 'A' AND 'Z'
OR @Char BETWEEN '0' AND '9' BEGIN
SET @String = STUFF( @String,
@Position, 1, @Char )
SET @Position = 9999
END
END
END
RETURN( @String )
END




Hope This Helps!

Ecobb
- I hate computers!

 

[Deam]

WOW. That works great. Thanks a lot.

 

[bdreed35]

This will also work for you, just quite a bit simpler (and smaller).

CREATE FUNCTION dbo.f_Proper_Case ( @v_IN_String  varchar(8000))
RETURNS VARCHAR(8000) AS  
BEGIN 
DECLARE
 @v_OUT_String 	varchar(8000)
 
set @v_IN_String = Lower(@v_IN_String) + ' '
set @v_OUT_String = ''
 
while ( charindex(' ',@v_IN_String,1) > 0)
 begin
      set @v_OUT_String = @v_OUT_String + Upper(left(@v_IN_String,1)) + substring(@v_IN_String,2,charindex(' ',@v_IN_String,1) - 1)
      set @v_IN_String = right(@v_IN_String, len(@v_IN_String) - charindex(' ',@v_IN_String,1) + 1)
 end
 
RETURN( @v_OUT_String )
END

~Brian

 

[rlevasseur]

I want to thank Ecobb and Brian for posting such a "nifty"
and useful function. I had been searching high and low for some sort of sub-routine that could be used to change all CAPS string fields to Mixed in SQL.

I'm involved in a project to convert a lot of my company's customer info from all CAPS (old mainframe holdover) to more eye pleasing Mixed case.

I had tried all sorts of function nesting, using cursors, but to no avail. These two postings do just about everything I want,

Many thanks again
Ray