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 …
create FUNCTION PROPERCASE ( --The string to be converted to proper case @input varchar(8000) ) --This function returns the proper case string of varchar type RETURNS varchar(8000) AS BEGIN IF @input IS NULL BEGIN --Just return NULL if input string is NULL RETURN NULL END --Character variable declarations DECLARE @output varchar(8000) --Integer variable declarations DECLARE @ctr int, @len int, @found_at int --Constant declarations DECLARE @LOWER_CASE_a int, @LOWER_CASE_z int, @Delimiter char(3), @UPPER_CASE_A int, @UPPER_CASE_Z int --Variable/Constant initializations SET @ctr = 1 SET @len = LEN(@input) SET @output = '' SET @LOWER_CASE_a = 97 SET @LOWER_CASE_z = 122 SET @Delimiter = ' ,-' SET @UPPER_CASE_A = 65 SET @UPPER_CASE_Z = 90 WHILE @ctr BEGIN --This loop will take care of reccuring white spaces WHILE CHARINDEX(SUBSTRING(@input,@ctr,1), @Delimiter) > 0 BEGIN SET @output = @output + SUBSTRING(@input,@ctr,1) SET @ctr = @ctr + 1 END IF ASCII(SUBSTRING(@input,@ctr,1)) BETWEEN @LOWER_CASE_a AND @LOWER_CASE_z BEGIN --Converting the first character to upper case SET @output = @output + UPPER(SUBSTRING(@input,@ctr,1)) END ELSE BEGIN SET @output = @output + SUBSTRING(@input,@ctr,1) END SET @ctr = @ctr + 1 WHILE CHARINDEX(SUBSTRING(@input,@ctr,1), @Delimiter) = 0 AND (@ctr BEGIN IF ASCII(SUBSTRING(@input,@ctr,1)) BETWEEN @UPPER_CASE_A AND @UPPER_CASE_Z BEGIN SET @output = @output + LOWER(SUBSTRING(@input,@ctr,1)) END ELSE BEGIN SET @output = @output + SUBSTRING(@input,@ctr,1) END SET @ctr = @ctr + 1 END END RETURN @output END GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO
So all that was required to get this to work, was to call the UDF after it was created …
--Insert your call here ... UPDATE TABLENAME SET fieldname=dbo.PROPERCASE(fieldname) FROM 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.
Latest posts by Edward (see all)
- Java Swing Model View Adapter Mediator - September 14, 2015
- Tomcat 8 on Java 8 Behind Apache on CentOS - April 16, 2015
- Getting a Primitive Data Types Wrapper Class in Java - November 13, 2014