Change MySQL text to Proper Case

Posted on 2009-04-29
Last Modified: 2013-12-07

How to change case for text in MySQL proper case.

Some texts are written ok but others are written like: HELLO WORLD

I want it to be Hello World

Is there any statement that will do it?

Also how to change values for columns with double to be just integer.
for example 59.78 I want it to be 59 or even 60 it doesn't matter much since it will be an integer.

It's stored in MySQL as VARCHAR in the table and I can't change it to a number.

Question by:majdolyan
    LVL 15

    Expert Comment

    Proper case is a tad tricky using just SQL, the following method only works for a single word. The general consensus seems to be to use a scripting language outside of SQL.

    As for the converting a varchar representing a double to an integer, use the following
    SET @foo = "HELLO";
    SELECT CONCAT(UCASE(LEFT(@foo, 1)), LCASE(SUBSTR(@foo, 2)));
    // Returns Hello
    SET @char = '1234.6';
    // Returns 1235

    Open in new window


    Author Comment


    Thanks for the tricks.

    both examples will work for only one value, how can I implement them for a complete column?
    LVL 15

    Accepted Solution

    Just use them in your select query.
      CONCAT(UCASE(LEFT(fldText, 1)), LCASE(SUBSTR(fldText, 2))) AS `Proper Text`,
      CAST(ROUND(fldDoubleText, 0) AS SIGNED) AS `Rounded Int`
    FROM tblData

    Open in new window


    Featured Post

    PRTG Network Monitor: Intuitive Network Monitoring

    Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

    Join & Write a Comment

    How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
    Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
    This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
    This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

    734 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now