SQL query to alter text in "from" field

[fredrikse]

Hi,

I'm about to migrate my current vBulletin to Xenforo. Right now I'm taking care of the last preparations. One thing I need to do is to run a sql query on some user fields to make all letters non capital except for the first letter that should be capital. Is this possible with help of an SQL query?

 

[Jake Bunce]

There is no such MySQL function. Custom programming is required... either PHP or SQL. Example:

http://joezack.com/index.php/2008/10/20/mysql-capitalize-function/

 

[fredrikse]

I also found this:

http://www.thingy-ma-jig.co.uk/blog/30-09-2010/mysql-how-upper-case-words

But I can't find a way to create functions in MySQL so I guess I'm left with your suggestion on this one.

 

[Brahimium]

If you are happy with a space being the only word delimiter, you could do this:

SELECT
title,
 
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
CONCAT(UPPER(SUBSTRING(title, 1, 1)), LOWER(SUBSTRING(title, 2)))
, ' a', ' A')
, ' b', ' B')
, ' c', ' C')
, ' d', ' D')
, ' e', ' E')
, ' f', ' F')
, ' g', ' G')
, ' h', ' H')
, ' i', ' I')
, ' j', ' J')
, ' k', ' K')
, ' l', ' L')
, ' m', ' M')
, ' n', ' N')
, ' o', ' O')
, ' p', ' P')
, ' q', ' Q')
, ' r', ' R')
, ' s', ' S')
, ' t', ' T')
, ' u', ' U')
, ' v', ' V')
, ' w', ' W')
, ' x', ' X')
, ' y', ' Y')
, ' z', ' Z')
 
FROM tablename;

Or just add the other delimiters as well ...
I don't know if there is a limit on how deep nested functions can go