Formatting initial caps 2

[travisbrown]

I quickly needed to clean up some data before reporting - making city names proper case, etc.

This is how I did it, but is there a better way just for future reference? I guess with 2005 and greater you could use REGEX.

REPLACE(LEFT(city,1),LEFT(city,1),UPPER(LEFT(city,1))) + RIGHT(city,LEN(city)-1)

I know it's not truly proper case, rather, initial caps, but it was all I needed for the moment.

 

[softhemc]

A bit shorter ...

Upper(Left(city,1)) + Right(city, Len(city) - 1)

 

[gmmastros]

Here is the ProperCase function I have in my database.

Create Function [dbo].[ProperCase](@Text as varchar(8000))
returns varchar(8000)as
begin   
declare @Reset bit;   
declare @Ret varchar(8000);   
declare @i int;   
declare @c char(1);   
select @Reset = 1, @i=1, @Ret = '';      
while (@i <= len(@Text))   	
	select @c= substring(@Text,@i,1),               
			@Ret = @Ret + case when @Reset=1 then UPPER(@c) else LOWER(@c) end,               
			@Reset = case when @c like '[a-zA-Z]' then 0 else 1 end,               
			@i = @i +1   
return @Ret
end

This should accommodate cities with multiple words. For example, "SAINT MARY OF THE WOODS, IN"

[tt][blue]Select dbo.ProperCase('SAINT MARY OF THE WOODS')[/blue][/tt]

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[markros]

http://blog.sqlauthority.com/2007/0...onvert-text-string-to-title-case-proper-case/

I was expecting SQL Server 2008 to have this function already.

 

[gmmastros]

I guess I must have recommended that same page to gooser at some point. Hmmm... Why didn't I remember that?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[markros]

I found another one, don't know why I didn't remember it today

http://berezniker.com/content/pages...ver/proper-udf-capitalize-string-proper-names

In any case, seems like most logical thing to just add this function to T-SQL.