Textpattern CMS support forum
You are not logged in. Register | Login | Help
- Topics: Active | Unanswered
Changing case from UPPERCASE to Title Case for certain feilds
This is probably a database question, and I wonder if a quick SQL query might solve the problem:
My client has entered a massive amount of text data in certain fields using UPPERCASE. He wants the text displayed in Title Case instead, and he doesn’t want to manually change it. I can easily use the style declaration text-transform: capitalize; for these elements, but my client would very much prefer formal title case in which words such as “the,” “of,” “and,” and “for” are not capitalized. (I see that Textmate’s “Convert to Titlecase” command does this correctly.)
These are the fields containing UPPERCASE data:
- Article category title (from the Categories tab)
- Article title (from the Write tab)
- custom_11 (also in the Write tab; this is a custom field defined using Gerhard Lazu’s
glz_custom_fields.)
I have Rob Sable’s admin DB manager active. Is there a simple SQL command or other method to convert the data in these fields to title case without manually poring through every article?
Thanks for your help! I welcome any insight folks are willing to share.
Offline
Re: Changing case from UPPERCASE to Title Case for certain feilds
As far as I know, there isn’t a mySQL command/ function to change a string to Title or Proper case though there are ones that would convert the contents of your fields to lower case and then the first letter of each word to a capital letter. That falls short of your requirement to leave ‘the’, ‘of’ etc. in lower case.
However, I think this utility might do it for you. Obviously, you would wish to practise on copies of the database :-)!
Offline
Re: Changing case from UPPERCASE to Title Case for certain feilds
Thanks, Joe.
If I had access to a Windows machine, I would test out the utility you mentioned, but at this time I’m limited to Unix/Mac OS 10.4.11. I did a few quick searches using based on your recommendation, but I couldn’t find anything for Mac.
I do operations similar to this frequently with text documents, and I was thinking that this could be achieved by using a Find and Replace with a regular expression, but I would need help 1) creating the regular expression, and 2) writing a Find and Replace command in SQL to target data in the specified fields.
Is this possible? Or is MySQL data fundamentally different than plain text?
Offline
#4 2008-08-08 07:53:46
- redbot
- Plugin Author
- Registered: 2006-02-14
- Posts: 1,410
Re: Changing case from UPPERCASE to Title Case for certain feilds
Have you thought to use hcg_templates to export all your database as a text file, make your changes and then reimport your data? I have not tested it, but from what I read somewere on the forum it should work.
Offline
#5 2008-08-08 12:12:25
- uli
- Moderator
- From: Cologne
- Registered: 2006-08-15
- Posts: 4,306
Re: Changing case from UPPERCASE to Title Case for certain feilds
redbot wrote:
Have you thought to use hcg_templates
It won’t work, unfortunately it just handles pages, forms and styles.
But until someone with the right regexp chimes in, maybe this blog post gets you where you want to go.
Edit: Nope, just don’t treats “of, the, …”
Last edited by uli (2008-08-08 12:15:33)
In bad weather I never leave home without wet_plugout, smd_where_used and adi_form_links
Offline
#6 2008-08-08 13:00:48
- masa
- Member
- From: Asturias, Spain
- Registered: 2005-11-25
- Posts: 1,091
Re: Changing case from UPPERCASE to Title Case for certain feilds
Not exactly what you’re looking for, but take a look at this Perl script
You might be able to extract the regular expressions and adapt for your own purposes.
Last edited by masa (2008-08-08 13:01:04)
Offline
Re: Changing case from UPPERCASE to Title Case for certain feilds
johnstephens wrote:
Thanks, Joe.
If I had access to a Windows machine, I would test out the utility you mentioned, but at this time I’m limited to Unix/Mac OS 10.4.11. I did a few quick searches using based on your recommendation, but I couldn’t find anything for Mac.
I do operations similar to this frequently with text documents, and I was thinking that this could be achieved by using a Find and Replace with a regular expression, but I would need help 1) creating the regular expression, and 2) writing a Find and Replace command in SQL to target data in the specified fields.
Is this possible? Or is MySQL data fundamentally different than plain text?
John,
It is possible to achieve what you want using a series of my SQL commands on the appropriate text fields in your database. In outline the procedure would be to run Select statements to:- convert the uppercase to lower case
- convert the first letter of each word to uppercase
- re-convert first letter of words like ‘the’, ‘a’, ‘of’ etc. to lowercase.
1 and 2 can be achieved easily using standard commands. 3 isn’t too difficult in concept either but is in execution. I am guessing that one would have to construct the Select statement to include all the words that one wanted to have reduced to all lowercase. I suspect that this is more or less what the Win Utility does for you, hence its value. I’m sure your best bet would be to make a copy of the database using the DB Admin plug-in and get someone with a PC to process it for you using the utility.
Alternatively, if any one out there already has a list of words like ‘the’, ‘of’ that need to be all lower case and can be pasted into the Select statement , the whole thing becomes easier to do at the mySQL interface.
When you make a backup copy of a mySQL database using the RSS plug in, the .sql option is plain text and consists of- mySQL Select statements to reconstitute the structure of the database and its tables
- data dump text which is the content of the fields.
You can, using mySQL, easily export only the fields you want converted to title case to a text .sql file (not zipped) and probably process it in Textmate and then re-import it to the database to replace the existing records. Obviously, all this needs to be done using copies of your database and requires some mySQL knowledge to export and reimport the requisite data. mySQL issues a series of free utilities for most platforms that includes a GUI based query builder that would ease your path in this respect.
Hope this helps.
Last edited by joebaich (2008-08-08 13:31:32)
Offline
Re: Changing case from UPPERCASE to Title Case for certain feilds
joebaich wrote:
You can, using mySQL, easily export only the fields you want converted to title case to a text .sql file (not zipped) and probably process it in Textmate and then re-import it to the database to replace the existing records. Obviously, all this needs to be done using copies of your database and requires some mySQL knowledge to export and reimport the requisite data. mySQL issues a series of free utilities for most platforms that includes a GUI based query builder that would ease your path in this respect.
That’s a great idea. I’ll give it a try. Thanks!
Offline
Re: Changing case from UPPERCASE to Title Case for certain feilds
Okay, this is what I found:
(91,‘2008-08-03 05:20:57’,‘txp-username’,‘2008-08-03 05:26:33’,’‘,‘UPPERCASE DATA’,’‘,’‘,’‘,’‘,’‘,’‘,‘UPPERCASE-DATA’,’‘,0,‘Comment’,0,4,1,1,‘section-name’,’‘,‘anthropology’,‘lowercase-data’,’‘,’‘,’‘,’‘,’‘,’‘,’‘,’‘,’‘,’‘,‘UPPERCASE DATA’,’‘,‘lowercase data’,‘3p344629nn178n874sp2s222s0q3n072’,‘2008-08-03’)
Each article record in the MySQL dump is formatted like this. I don’t know how to target the uppercase data for a “Convert to Titlecase” action. If I have to select each instance of 'UPPERCASE DATA' or 'UPPERCASE-DATA' and “Convert to Titlecase,” I’m better off editing each record in Textpattern— even with 10,000 entries.
Maybe I can get help from the folks at Textmate— I’ve never asked before, and this community is so consistently helpful that it was natural for me to turn here first. If I can’t find anything, I’ll come back— but if I get some useful information, I’ll post the results here.
Thanks everyone again for your guidance!
Offline
#10 2008-08-08 19:44:12
- cnewkirk
- Plugin Author
- From: Bakersfield, Ca
- Registered: 2006-01-04
- Posts: 21
Re: Changing case from UPPERCASE to Title Case for certain feilds
masa wrote:
Not exactly what you’re looking for, but take a look at this Perl script
You might be able to extract the regular expressions and adapt for your own purposes.
I just did that, (kind of, I used a Wordpress plugin as a starting point)…
Of course this doesn’t change the database, just how the text is displayed.
Offline
#11 2008-08-08 20:02:36
- masa
- Member
- From: Asturias, Spain
- Registered: 2005-11-25
- Posts: 1,091
Re: Changing case from UPPERCASE to Title Case for certain feilds
Excellent, Chris – thanks very much!
Offline
Re: Changing case from UPPERCASE to Title Case for certain feilds
johnstephens wrote:
Okay, this is what I found:
(91,‘2008-08-03 05:20:57’,‘txp-username’,‘2008-08-03 05:26:33’,’‘,‘UPPERCASE DATA’,’‘,’‘,’‘,’‘,’‘,’‘,‘UPPERCASE-DATA’,’‘,0,‘Comment’,0,4,1,1,‘section-name’,’‘,‘anthropology’,‘lowercase-data’,’‘,’‘,’‘,’‘,’‘,’‘,’‘,’‘,’‘,’‘,‘UPPERCASE DATA’,’‘,‘lowercase data’,‘3p344629nn178n874sp2s222s0q3n072’,‘2008-08-03’)
Each article record in the MySQL dump is formatted like this. I don’t know how to target the uppercase data for a “Convert to Titlecase” action. If I have to select each instance of
'UPPERCASE DATA'or'UPPERCASE-DATA'and “Convert to Titlecase,” I’m better off editing each record in Textpattern— even with 10,000 entries.
John,
The output above indicates that you selected ALL the fields in your query; it’s the default if you used the RSS DB Backup plug-in. Instead, do a query that selects only the fields that are in UPPERCASE that need to be converted to Title Case; Article category title (from the Categories tab), Article title (from the Write tab) and custom_11 (also in the Write tab; this is a custom field defined using Gerhard Lazu’s glz_custom_fields.) That will give you output that consists of all the data that needs to be processed and it should be a snap with Textmate. You can then use that to UPDATE the target fields. It may seem to be a tall order if you are unfamiliar with mySQL but that’s what mySQL does best. The free query builder that mySQL provides can help you construct and run the queries.
Offline