MySQL String函数等效于PHP ucwords()函数 [英] MySQL String Function equivalent to PHP ucwords() function

问题描述

可能重复:
MySQL-在现有表格中将每个单词的首字母大写

Possible Duplicate:
MySQL - Capitalize first letter of each word, in existing table

是否存在与PHP ucwords() 函数等效的MySQL String函数.

Is there a MySQL String function equivalent to PHP ucwords() function.

最终目标是使用MySQL将字符串中每个单词的首字母大写.

The end goal is to use MySQL to uppercase the first letter of each word in a string.

示例.

-- The name field in the table holds "JOHN DOE"
SELECT LOWER(name)
  FROM table

这将给我结果'john doe'

我希望结果为'John Doe'

推荐答案

本文向您展示了如何在MySQL中定义适当的情况:

This article shows you how to define a Proper Case in MySQL: Proper Case.

包含链接腐烂的情况:

DROP FUNCTION IF EXISTS proper; 
SET GLOBAL  log_bin_trust_function_creators=TRUE; 
DELIMITER | 
CREATE FUNCTION proper( str VARCHAR(128) ) 
RETURNS VARCHAR(128) 
BEGIN 
  DECLARE c CHAR(1); 
  DECLARE s VARCHAR(128); 
  DECLARE i INT DEFAULT 1; 
  DECLARE bool INT DEFAULT 1; 
  DECLARE punct CHAR(18) DEFAULT ' ()[]{},.-_\'!@;:?/'; -- David Rabby & Lenny Erickson added \' 
  SET s = LCASE( str ); 
  WHILE i <= LENGTH( str ) DO -- Jesse Palmer corrected from < to <= for last char 
    BEGIN 
      SET c = SUBSTRING( s, i, 1 ); 
      IF LOCATE( c, punct ) > 0 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; 
          ELSEIF c >= '0' AND c <= '9' THEN 
            SET bool = 0; 
          END IF; 
        END; 
      END IF; 
      SET i = i+1; 
    END; 
  END WHILE; 
  RETURN s; 
END; 
| 
DELIMITER ; 
select proper("d'arcy"); 
+------------------+ 
| proper("d'arcy") | 
+------------------+ 
| D'Arcy           | 
+------------------+ 

这篇关于MySQL String函数等效于PHP ucwords()函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！