using mysql string functions

I have a table with following Data in a table

abcd 
abcd , pgw 
ABcd , Pgw

I want output as

Abc开发者_开发问答d
Abcd , Pgw
Abcd , Pgw

the First letter in capitals and letter after ',' in capital.

---

to make first letter upper-case use this

select CONCAT(UPPER(LEFT(`field`, 1)), SUBSTRING(`field`, 2)) from mytable

to do it to also to word after comma declare a function like :

DELIMITER ||  

CREATE FUNCTION `ucwords`( str VARCHAR(128) ) RETURNS VARCHAR(128) CHARSET latin1  
BEGIN  
  DECLARE c CHAR(1);  
  DECLARE s VARCHAR(128);  
  DECLARE i INT DEFAULT 1;  
  DECLARE BOOL INT DEFAULT 1;        
  SET s = LCASE( str );  
  WHILE i < LENGTH( str ) DO  
     BEGIN  
       SET c = SUBSTRING( s, i, 1 );  
       IF c = ',' THEN  
        SET BOOL = 1;  
      ELSEIF BOOL=1 THEN  
        BEGIN  
          IF c >= 'a' AND c <= 'z' THEN  
             BEGIN  
               SET s = CONCAT(LEFT(s,i-1),UCASE(c),SUBSTRING(s,i+1));  
               SET BOOL = 0;  
             END;             
          END IF;  
        END;  
      END IF;  
      SET i = i+1;  
    END;  
  END WHILE;  
  RETURN s;  
END ||  

DELIMITER ;

and use

 select ucwords(`field`) from mytable

---

Please don't do this if you ever expect your database to scale well. If you're only ever going to be using this for your personal phone book application or equally tiny data sets, you can get away with doing this at select time, as you suggest.

But, if you want a robust scalable database, you should sacrifice space (disk storage) for time (speed). Per-row functions on select statement rarely scale well.

One time-honored and tested way of doing this is to add another column of identical specifications to the one you already have and use an insert/update trigger to populate it with the data in the format you desire.

Then the cost of the transformation is incurred only when you have to (when the data is updated), not every single time you read the data. This amortises the cost across the reads which, in the vast majority of databases, outweigh writes considerably.

This answer shows an SQL formula which seems close to what you want but I do strongly suggest that you use it in a trigger rather than inefficiently running it every select.

---

take a look at this solution mysql-update-script-problem-all-txt-is-lower-case-just-first-letter-is-upper-cas , it should give you enough information to adapt to what you're trying to do. Change update to SELECT, etc. etc.