SQL UDF to Convert Uppercase to Propercase
So, I figured that it would be a good idea to try and expand Florida Search Engine Optimization web coverage beyond the Miami, Fort Lauderdale areas. So, I started a project to build a quazi regional network in my site. At first, I tried to be creative and store all of the cities and counties of Florida in an array with the counties as an array of structs.
With some work, I was able to get this running. However, I realized that if and when I want to make changes or additions to the cities or counties, it was going to be quite a bit of work.
After some consideration I decided that it would be much easier to just use a database table with the cities and counties.
Now, everything was going quite well with the addition of the new database when I found that the city and county fields were all in uppercase ... I really didn't want to leave the data like that, so I decided to figure out how to change the 80,000 plus records to propercase, and I found just the thing ... a cool little T-SQL UDF ... that changes the data to propercase ... the code follows ...
2(
3--The string to be converted to proper case
4@input varchar(8000)
5)
6--This function returns the proper case string of varchar type
7RETURNS varchar(8000)
8AS
9BEGIN
10IF @input IS NULL
11BEGIN
12--Just return NULL if input string is NULL
13RETURN NULL
14END
15
16--Character variable declarations
17DECLARE @output varchar(8000)
18--Integer variable declarations
19DECLARE @ctr int, @len int, @found_at int
20--Constant declarations
21DECLARE @LOWER_CASE_a int, @LOWER_CASE_z int, @Delimiter char(3), @UPPER_CASE_A int, @UPPER_CASE_Z int
22
23--Variable/Constant initializations
24SET @ctr = 1
25SET @len = LEN(@input)
26SET @output = ''
27SET @LOWER_CASE_a = 97
28SET @LOWER_CASE_z = 122
29SET @Delimiter = ' ,-'
30SET @UPPER_CASE_A = 65
31SET @UPPER_CASE_Z = 90
32
33WHILE @ctr <= @len
34BEGIN
35--This loop will take care of reccuring white spaces
36WHILE CHARINDEX(SUBSTRING(@input,@ctr,1), @Delimiter) > 0
37BEGIN
38SET @output = @output + SUBSTRING(@input,@ctr,1)
39SET @ctr = @ctr + 1
40END
41
42IF ASCII(SUBSTRING(@input,@ctr,1)) BETWEEN @LOWER_CASE_a AND @LOWER_CASE_z
43BEGIN
44--Converting the first character to upper case
45SET @output = @output + UPPER(SUBSTRING(@input,@ctr,1))
46END
47ELSE
48BEGIN
49SET @output = @output + SUBSTRING(@input,@ctr,1)
50END
51
52SET @ctr = @ctr + 1
53
54WHILE CHARINDEX(SUBSTRING(@input,@ctr,1), @Delimiter) = 0 AND (@ctr <= @len)
55BEGIN
56IF ASCII(SUBSTRING(@input,@ctr,1)) BETWEEN @UPPER_CASE_A AND @UPPER_CASE_Z
57BEGIN
58SET @output = @output + LOWER(SUBSTRING(@input,@ctr,1))
59END
60ELSE
61BEGIN
62SET @output = @output + SUBSTRING(@input,@ctr,1)
63END
64SET @ctr = @ctr + 1
65END
66
67END
68RETURN @output
69END
70
71GO
72SET QUOTED_IDENTIFIER OFF
73GO
74SET ANSI_NULLS ON
75GO
So all that was required to get this to work, was to call the UDF after it was created ...
2RETURN @output
3END
4
5GO
6SET QUOTED_IDENTIFIER OFF
7GO
8SET ANSI_NULLS ON
9GO
10
11--Insert your call here ...
12UPDATE TABLENAME
13SET fieldname=dbo.PROPERCASE(fieldname)
14FROM DBNAME..TABLENAME
Now this saved me a hell of a lot of time and was a very simple and easy way to format the data the way that I wanted. Unfortunately, when I went to repeat the same action for the counties, I accidentally wrote over the city fields with the data in the counties field ... Smooth.
It would really come in handy if something like this could have been done in ColdFusion without having to create a UDF to do so ... maybe the guys over at Adobe will read this and decide that it merits inclusion into the next release.
This will run much faster in SQL, especially as you deal with larger recordsets.
Geoff ... This UDF ran through all 80,000 records in 7 or 8 seconds ... That's really fast for such a big change.
