How to find lowercase (no caps) values in mySQL (and Capitalize them with ColdFusion)
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:
SELECT user_city, ID
FROM site_users
WHERE user_city = lower(user_city)
AND NOT user_city = ''
ORDER 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:
<cfoutput query="myQueryAbove">
<cfquery datasource="#application.dsn#">
UPDATE site_users
SET user_city = '#capFirstTitle(myQueryAbove.user_city)#'
WHERE ID = #myQueryAbove.ID#
</cfquery>
</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.
There are no comments for this entry.
Add Comment