1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

SQL query to alter text in "from" field

Discussion in 'XenForo Questions and Support' started by fredrikse, Jan 6, 2013.

  1. fredrikse

    fredrikse Active Member

    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?
     
  2. Jake Bunce

    Jake Bunce XenForo Moderator Staff Member

    fredrikse likes this.
  3. fredrikse

    fredrikse Active Member

  4. Brahimium

    Brahimium Member

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

    Code:
    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 :)
     

Share This Page