问题描述:

I figured id start with this;

UPDATE wp_users

SET Name = REPLACE(Name, '.',' ')

But then i realised nowhere can i specify its only one column i want to affect, the display_name column, first i need to replace a '.' with a ' ' (space), then i want to either replace a with A, or assuming that'd be alot of duplicated SQL, capitalise first letter of each word.

Im using MySQL with wordpress

Summary: Data is currently:

**display_name**

joe.bloggs

sally.sue

timmy.turner

I need to get it to be:

**display_name**

Joe Bloggs

Sally Sue

Timmy Turner

网友答案:

Mysql is really bad with these things. I used substring index , left, right and some concatenation to make the following. It look bit ugly but works. It wont works for name like van.der.sar

SELECT CONCAT_WS(' ',
    CONCAT(
                UCASE(LEFT(SUBSTRING_INDEX('john.doe','.',1),1)),
               RIGHT(SUBSTRING_INDEX('john.doe','.',1),length(SUBSTRING_INDEX('john.doe','.',1))-1)
        ),
       CONCAT(
                UCASE(LEFT(SUBSTRING_INDEX('john.doe','.',-1),1)),
               RIGHT(SUBSTRING_INDEX('john.doe','.',-1),length(SUBSTRING_INDEX('john.doe','.',-1))-1)
        )           
       )

so your update script should be like below

Update wp_users set name = CONCAT_WS(' ',
        CONCAT(
                    UCASE(LEFT(SUBSTRING_INDEX(name,'.',1),1)),
                   RIGHT(SUBSTRING_INDEX(name,'.',1),length(SUBSTRING_INDEX(name,'.',1))-1)
            ),
           CONCAT(
                    UCASE(LEFT(SUBSTRING_INDEX(name,'.',-1),1)),
                   RIGHT(SUBSTRING_INDEX(name,'.',-1),length(SUBSTRING_INDEX(name,'.',-1))-1)
            )           
           )
相关阅读:
Top