Okay, so I can do this in PHP:
echo ucwords(strtolower('heWletT paCKarD'));
Output:
Hewlett Packard
That's all well and good, but how do I do the same thing in MySQL?
To get started I have tried this:
SELECT UPPER(LOWER('heWletT paCKarD'))
But that just sets the string to lower case - then upper case, giving me:
HEWLETT PACKARDCan anyone help?
INITCAP (string), might work
Since you're not using Oracle, you'll have to implement a workaround for MySQL; here is one suggestion for doing so.
EDIT: Note that the solution would get rather complicated if you have a variable number of words; it might just be best to continue to use PHP's ucfirst() function.
oops this is an oracle special....my bad.
Here is a nice SQL function that I dug up on the net. It supposedly works, but doing it in PHP as brad suggested will be preferable imo, too.
1.
2.DELIMITER $$
3.
4.DROP FUNCTION IF EXISTS INITCAP$$
5.
6.CREATE FUNCTION INITCAP( param VARCHAR(255) )
7. RETURNS VARCHAR(255)
8. /*
9. * Author : Jens Blawatt
10. * Website : http://www.Blawatt.de
11. * Description : A MySQL equivalent to ORACLE’s initcap
12. */
13. BEGIN
14. DECLARE result VARCHAR(255) DEFAULT ”;
15. DECLARE tmp VARCHAR(255) DEFAULT ”;
16.
17. – endless repeat
18. WHILE 1 = 1 DO
19. – if it’s the end of the blank spearated string
20. IF INSTR(TRIM(param) , ‘ ‘) = 0 THEN
21. RETURN TRIM(CONCAT(result, UCASE(LEFT(param,1)),LOWER(SUBSTR(param,2))));
22. END IF;
23.
24. – split the first part to tmp
25. SET tmp = SUBSTR(param, 1, INSTR(param , ‘ ‘));
26.
27. – write first character in capital letter rest in small type
28. SET result = CONCAT(result, UCASE(LEFT(tmp,1)),LOWER(SUBSTR(tmp,2)));
29.
30. – remove splitted word from param string
31. SET param = SUBSTR(param, INSTR(param , ‘ ‘) + 1);
32. END WHILE;
33.END$$
34.
35.DELIMITER ;I actually used a function called "propper".
I have renamed it to ucowrds to be in-line with my php.
The reason I wanted this is so that I didn't have to use PHP to do this task as doing it in MySQL alone is soooooo much faster.
According to the MySQL gurus on MySQL.com, you should never pull data from a database unless you are going to use it externally.
The idea of a relational database is that you can do all internal data processing INTERNALLY without involving a programming language.
Anyway, the function I used is here.
http://forums.mysql.com/read.php?20,265978,266040#msg-266040
iceomnia wrote:According to the MySQL gurus on MySQL.com, you should never pull data from a database unless you are going to use it externally.
That is obvious. It is like saying that you should never write meaningless code.
iceomnia wrote:The idea of a relational database is that you can do all internal data processing INTERNALLY without involving a programming language.
I'd say that that happens to be a useful aspect of database software rather than the idea of a relational database since getting the database software to do data processing work is orthogonal to the relational model.
Consequently, if it turns out that it is useful to not use the database software for certain data processing, go ahead and use the alternative.
Oh yeah - agreed.
My point was that you would never pull data from a database to change something in a programing language if the same task can be done in the database it's self.
iceomnia wrote:if the same task can be done in the database it's self/quote true, but considering that the solution you linked to above involved creating a function in MySQL ~845 characters in length, I'm personally much more inclined to stick with typing ucwords($row['foo']);.
Plus, one might argue that capitalizing the first letter of each word is a stylization/formatting of the data in the display process; to me, there's a point where the DBMS' responsibility for collating and relating data ends and the receiving application (e.g. your PHP script) is left to do whatever it pleases to markup the data further for display purposes.
Long story short... "to each his own" I suppose? :p
Yes but the query was as follows:
UPDATE `manufacturers` SET `manuname` = ucwords(`manuname`);
To do this with PHP you would have to:
On 12,000 rows this used to take PHP/MySQL 2 minutes or so ( sometimes longer depending on current server load)
The query above does the job in 0.0543 seconds.
The problem is that our database is full of messy brand names, some in Upper case, some in lower case and some are even mixed.
Running this query every night (on a cron job) takes half a second (not two minutes) and means that we don't have to style it when it's retrieved, making for less processing time on the front-end of the site.
If you're going for uniformity, why not just modify the applicatiton(s) that modify/insert data in this table to first use [man]ucwords/man on the data? That would both keep the processing away from the front end and remove the need for a cron job.
Yeah, I agree, that would make sense.
The data comes in from a giant data feed, some files and some web services and is inserted straight into the database.
We may look at conditioning the data prior to insert at a later date but that still wouldn't have helped the current problem for the data that was already in the database.