bulk edit products to titlecase mysql window

Changing Posts or Products from ALL CAPS to Title Case / Proper Case

In B2B Advertising, Business to Business Advertising, Internet Development, Uncategorized, web development by Myke Amend

Why would anyone need to do a bulk conversion of data to title case or proper case?

WordPress websites often have multiple editors, each with their own style of writing and formatting posts.

This, unfortunately, can cause a website to be disjointed, and is the primary reason web designers like to leave things like text colors, heading colors, paragraph alignment and other needed constants to the stylesheets of the website. This set formatting is actually one of the great things about WordPress sites (and most any other site of today), where editors and authors can just select between paragraph format, heading formats, bold, italic, blockquote where needed and leave the design to the actual designer via stylesheets.

But sometimes, for one reason or another, a webmaster might find that titles or even content for some products or posts are in ALL CAPS on the site, and this can happen for a number of reasons. I think I have experienced all of them by now.

  1. The editor or author sees that all titles are in ALL CAPS and assumes that they need to be typed that way when this is actually a matter of that text being transformed via text transform in the stylesheet, not how the information is typed.
  2. Products or other information are brought in from a spreadsheet, where for some reason or another, the original data is in all caps. The last time I ran into this is was because the data from Quickbooks was in this format, because it was also used for parts labels, and they found this format to be easier to read on small labels.
  3. The editor or author believes their post to be more important than all other posts, and seeks to communicate this by typing with the caps lock on, or maybe they are *that* person who does not know there is a caps lock button they can turn off… you know… the one who is always emailing you forwards and chain letters… shares all those images beginning with “share if… like if…”. There is no easy fix, as far as data goes, if that editor decided to make everything green and red text in  Papyrus and Comic Sans for impact – but all caps: We can fix that.

When you find that you have 3,000+ products that might be penalized as Google shopping ads for looking “spammy” with everything in upper case – I suppose you can hire an intern to do all the tedious (and even still very expensive) editing if you want to give the client sticker shock and don’t mind wasting their money. You could also ask the client to give you a corrected list to import back in, “when they have the time” (hint: nobody on most any payroll has that sort of time). For us, when you are trying to show your worth by making their Adwords really work for them, you know this needs done right away, and cheaply.

Whatever reason you might have for needing to do this sort of bulk replacement, I hope this information is useful to you.

Sometimes when it comes to bulk changing data, especially where concatenating or math is involved, I tend to like to work in Excel on exported data, and then re-import it.

For this operation however, Excel really won’t do the trick, and there really is no simple query for this (yet). We also want to be able to do this again after products are imported again, because we will certainly be working again with the same ALL CAPS data and wanting to correct it quickly.

Bulk data conversion and repeatability

One reliable way I have found to have this is by adding a routine on the mySQL side of things. This is an operation that is saved to the database, and can be run on any table and any column later on. I have in the past, especially for complicated changes simply made a page that could be uploaded, executed, and deleted. If it is something I need often enough for enough sites I might write a plugin, but in those cases where an operation might need to be repeated for one particular site, I feel routines are the best way to go.

In this example, I am only editing the titles, and only the titles of products and product variations. You could use this same operation to change post content, or just about any other column of any other table.

First, is our routine – you can go into PHP Myadmin and add it there under the table’s SQL tab up  top (code below). Just paste the code and click ‘go’ to add this routine to your database.

sql routine to convert to title case

CREATE FUNCTION TITLE_CASE (input VARCHAR(255))

RETURNS VARCHAR(255)

DETERMINISTIC

BEGIN
	DECLARE i INT;
	DECLARE titlelength INT;
	
	SET titlelength   = CHAR_LENGTH(input);
	SET i = 0;
	SET input = LOWER(input);

	WHILE (i < titlelength) DO
		IF (MID(input,i,1) = ' ' OR i = 0) THEN
			IF (i < titlelength) THEN
				SET input = CONCAT(
					LEFT(input,i),
					UPPER(MID(input,i + 1,1)),
					RIGHT(input,titlelength - i - 1)
				);
			END IF;
		END IF;
		SET i = i + 1;
	END WHILE;

	RETURN input;
END;

As you can see above, this is a psuedo title case. It turns everything lowercase and then sets the first character of every word to uppercase. Words like ‘in’ and ‘and’, if they exist in the titles will also begin with a capital letter. Sine we’re doing this for Google’s benefit, I am not too worried about that. If you are, you could opt to just change all instances of these words after the fact via a handful of queries.

Or.. you could give this approach a try: https://stackoverflow.com/questions/1191605/is-there-a-simple-way-to-convert-mysql-data-into-title-case . In that example, the routine can also force lowercase for articles (a, an, the) , coordinating conjunctions (and, but, for), and short (less than 5 letters) prepositions (at, by, from).

Next, for either case, it is always good to test the routine before running it on your database (I tend to go a step more cautious and export the database first… just in case).

To test this, just type the below into your SQL window and click ‘Go” (if you went with the other routine on StackOverflow you should test according to their instructions).

SELECT TITLE_CASE(
	'THIS IS IN ALL CAPS -now equals- This Is In All Caps!' 
)

After that, we can run our query with our new routine in the SQL window like so (if you went with the other routine on StackOverflow you should run that routine according to their instructions):

bulk edit products to title case mysql window

UPDATE `wp_posts` SET `post_title` = TITLE_CASE(`post_title`) WHERE `post_type` LIKE '%product%';

 

After this point, if everything went well, you should see a green background notification that says how many rows were affected, and that number should be more than zero.

You can return to run this routine time and time again if and as needed.

Myke is a full-stack web developer for The Industrial Web Development Team at Lohre & Associates, Inc.. He is also a fine-art painter and engraver, best known for his steampunk fantasy illustrations, and his “Infernal Device” project at Artprize.