Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2013
    Posts
    7

    Unanswered: Capitalize first letter of attributes in collumn

    Hi, i am a novice at SQL and am struggling to construct a query that turns all the data in the table 'lName' to only have a capital letter at the beginning and the rest to be lower case. It sounds so simple, yet finding it extremely difficult and frustrating. Please if someone can help i would really appreciate it.

    Until now this is what i got:

    SELECT UCASE(lName)+ LOWER(SUBSTRING(lName,2)
    FROM student;

  2. #2
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    In MySQL you need to use the function CONCAT to join two strings together. Also look at the LEFT function which should be used in conjunction with the UCASE function. Here is an example:

    Code:
    mysql> select * from names;
    +--------+------+
    | name   | age  |
    +--------+------+
    | TOM    |   20 | 
    | DICK   |   20 | 
    | HARRY  |   20 | 
    | JIMMY  |   24 | 
    | JOHNNY |   24 | 
    | MICKEY |   27 | 
    | MOE    |   27 | 
    +--------+------+
    7 rows in set (0.00 sec)
    
    mysql> select CONCAT(LEFT(UCASE(name),1), LCASE(SUBSTRING(name, 2))) name FROM names;
    +--------+
    | name   |
    +--------+
    | Tom    | 
    | Dick   | 
    | Harry  | 
    | Jimmy  | 
    | Johnny | 
    | Mickey | 
    | Moe    | 
    +--------+
    7 rows in set (0.00 sec)
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  3. #3
    Join Date
    Oct 2013
    Posts
    7
    Quote Originally Posted by it-iss.com View Post
    In MySQL you need to use the function CONCAT to join two strings together. Also look at the LEFT function which should be used in conjunction with the UCASE function. Here is an example:

    Code:
    mysql> select * from names;
    +--------+------+
    | name   | age  |
    +--------+------+
    | TOM    |   20 | 
    | DICK   |   20 | 
    | HARRY  |   20 | 
    | JIMMY  |   24 | 
    | JOHNNY |   24 | 
    | MICKEY |   27 | 
    | MOE    |   27 | 
    +--------+------+
    7 rows in set (0.00 sec)
    
    mysql> select CONCAT(LEFT(UCASE(name),1), LCASE(SUBSTRING(name, 2))) name FROM names;
    +--------+
    | name   |
    +--------+
    | Tom    | 
    | Dick   | 
    | Harry  | 
    | Jimmy  | 
    | Johnny | 
    | Mickey | 
    | Moe    | 
    +--------+
    7 rows in set (0.00 sec)
    Thank you soooo much mate : )

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •