I have a request from a client to help clean up some customer-entered data in a mySQL database, specifically, we need to capitalize all of the values in a column called 'user_city'

For the actual capitalization, I am using the very handy CapFirstTitle() function from CFlib : http://www.cflib.org/index.cfm?event=page.udfbyid&udfid=116

But first, I need to find all of the entries that are not already capitalized. This dataset has tens of thousands of records , including some with a blank value in the 'city' field - no need to loop all of that just to fix our caps!

So, here's the SQL query code to find all of the records with no capital letters at all, by comparing to a lower() all lowercase version of the same value:

view plain print about
1SELECT user_city, ID
2FROM site_users
3WHERE user_city = lower(user_city)
4AND NOT user_city = ''
5ORDER by user_city

And here is the full CFoutput / CFquery code for the loop and update with Coldfusion, and the CapFirstTitle() function in the head of the page:

view plain print about
1<cfoutput query="myQueryAbove">
2    <cfquery datasource="#application.dsn#">
3    UPDATE site_users
4    SET user_city = '#capFirstTitle(myQueryAbove.user_city)#'
5    WHERE ID = #myQueryAbove.ID#
6    </cfquery>
7</cfoutput>

for other columns you could use OR in the first query and an additional statement in the second, or just change the queries, run the page again, or even, make the column name a variable and just change it once in the head of the page... easy.