SQL UDF to Convert Uppercase to Propercase

on

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.

The following two tabs change content below.
Edward J. Beckett is a passionate software engineer, web developer, server administrator and polyglot programmer with nearly a decade experience building desktop and web applications ranging from simple personal web sites to enterprise level applications on many technology stacks including Java, Java EE, Spring, Spring MVC, Spring Data, Hibernate, SQL, JPA, JMS, HTML, CSS, JavaScript, ColdFusion, PHP, Node.js and more...
  • Geoff

    This will run much faster in SQL, especially as you deal with larger recordsets.

  • http://www.edwardbeckett.com Edward Beckett

    Geoff … This UDF ran through all 80,000 records in 7 or 8 seconds … That’s really fast for such a big change.