asked on

Is there a T-SQL command to convert fields in a SQL table from all uppercase to proper case?

HI,
I have a table in SQL that contains fields that happen to be in all uppercase. I am surprised this hasn't come up before, but the users are asking, rightly, if we can change the info to be properly cased. The data happens to be city and county fields related to zip codes. For example, zip code 46802 is in Fort Wayne (city) and county (Allen). But the table returns the values FORT WAYNE for city and ALLEN for county. Is there a command I could run against the data in the fields to change the words to only have the first character of each word be capitalized?

chapmandew

If it is just one word, then something like this:

update table
set firstname = upper(left(firstname,1)) + lower(substring(firstname, 2, len(firstname)-1))

wendelina

ASKER

Hi Chap,
So what would that do in the case where the field contained the string FORT WAYNE? Would I end up with Fort wayne?
I could test it - but - it is easier to ask you!
Thanks!
Wendy
PS - I had hoped there was just a ProperCase(variable) command that would just capitalize the first character of every "word" in the field. I am still hoping. : )

chapmandew

Yeah, I think this should do it for you. It may not be the fastest thing ever, but it should work. Call it like this:

create function udf_convertupper
(
	@string varchar(100)
)
returns varchar(100)
begin
	declare @len int, @i int, @newstring varchar(100)
	declare @char char(1), @prevchar char(1)
 
	set @len = LEN(ltrim(rtrim(@string)))
	set @i = @len - 1
	set @newstring = ''
	set @prevchar = ''
 
	while @i >= 0
	begin
		set @char = substring(@string, (@len-@i), 1)		
		set @newstring = @newstring + case when @prevchar = '' THEN UPPER(@char) else @char end
		set @prevchar = @char
		set @i = @i - 1
	end
	return(@newstring)
	
end
 
declare @string varchar(100)
set @string = 'fort wayne'
select dbo.udf_convertupper(@string)

Open in new window

wendelina

ASKER

Hi again,
That does look like it would do it - the only problem I can think of now is can I run this from the Query Analyzer screen in SQL? I had to choose the category SQL 2005 out of the EE list becuase they don't have SQL 2000 available as a choice anymore, but this particular DB table is still SQL 2000.
What do you suggest?
thanks
Adding some extra points becuase I guess I am expanding the question to include "how do I apply the solution?"
Wendy

chapmandew

Yes, you can run it from enterprise manager or query analyzer...it will work for both versions.

ASKER CERTIFIED SOLUTION

chapmandew

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

wendelina

ASKER

Does this code work if the fields are already in UPPER case, or does it only work if the field is lower case? I ran it, it looks like it affected the right number of rows, but the data looks the same as it did before.
Help?

wendelina

ASKER

Perfect. I ran a command to convert all the field to all lower case and then ran your function against the table. It now looks just right.
thank you!!!!

chapmandew

You're very welcome.

chapmandew

Please mark mine as the answer...if you don't mind. :)

wendelina

ASKER

Sorry I thought I closed this one out!
Thanks again and this is exactly what we were needing. A well spent couple of hours testing and documenting your code, and I hope to use it on our production system next week. THANKS.