MySQL LCASE() function

LCASE() function

MySQL LCASE() converts the characters of a string to lower case characters.

This function is similar to the function LOWER().

This function is useful in -

• Lowercase conversion: Lowercases all uppercase characters in an original string.
• Case-insensitive comparisons: LCASE() is often used with comparison operations to perform case-insensitive searches.
• Data normalization: LCASE() can be used to normalize the case of strings.

Syntax:

LCASE (string)

Argument:

Syntax Diagram:

MySQL Version: 8.0

Example of MySQL LCASE() function

The following MySQL statement converts all of the characters of the string 'MYTESTSTRING' to lower case characters and returns 'myteststring'.

Code:

SELECT LCASE('MYTESTSTRING');

Output:

mysql> SELECT LCASE('MYTESTSTRING');
+-----------------------+
| LCASE('MYTESTSTRING') |
+-----------------------+
| myteststring          | 
+-----------------------+
1 row in set (0.00 sec)

Example of MySQL LCASE() function using where

The following MySQL statement returns those rows from the publisher table which does not contain a publisher belong to the USA. The column pub_name and pub_name in lowercase are displayed in the output.

Code:

SELECT pub_name,LCASE(pub_name) 
FROM publisher 
WHERE country<>'USA'; 

Sample table: publisher

Output:

mysql> SELECT pub_name,LCASE(pub_name) 
    -> FROM publisher 
    -> WHERE country<>'USA';
+------------------------------+------------------------------+
| pub_name                     | LCASE(pub_name)              |
+------------------------------+------------------------------+
| BPP Publication              | bpp publication              | 
| New Harrold Publication      | new harrold publication      | 
| Ultra Press Inc.             | ultra press inc.             | 
| Pieterson Grp. of Publishers | pieterson grp. of publishers | 
| Novel Publisher Ltd.         | novel publisher ltd.         | 
+------------------------------+------------------------------+
5 rows in set (0.03 sec)

Case-insensitive search using LCASE() with WHERE clause

The following MySQL statement returns the publishers name with a certain substring in a case-insensitive manner.

Code:

SELECT pub_name FROM publisher 
WHERE LCASE(pub_name) 
LIKE '%pub%';

Sample table: publisher

Output:

pub_name                     |
-----------------------------+
Jex Max Publication          |
BPP Publication              |
New Harrold Publication      |
Mountain Publication         |
Summer Night Publication     |
Pieterson Grp. of Publishers |
Novel Publisher Ltd.         |

MySQL: Upper case the first letter and lower case the rest of a string

We have a table called test with following records:

mysql> SELECT * FROM test;
+-----------+
| test_char |
+-----------+
| abcd      |
| WxyZ      |
| scott     |
| ROBIN     |
+-----------+
4 rows in set (0.00 sec)

Now we want to update the above data where the first character will be in upper case and rest will be lower case i.e. 'abcd' will be 'Abcd', 'WxyZ' will be 'Wxyz' and so on. See the following MySQL statement:

mysql> UPDATE TEST SET test_char = CONCAT(UCASE(LEFT(test_char, 1)), LCASE(SUBSTRING(test_char, 2)));
Query OK, 4 rows affected (0.04 sec)
Rows matched: 4  Changed: 4  Warnings: 0
mysql> SELECT * FROM TEST;
+-----------+
| test_char |
+-----------+
| Abcd      |
| Wxyz      |
| Scott     |
| Robin     |
+-----------+
4 rows in set (0.00 sec)

Video Presentation:

All String Functions (Slides presentation)

Previous: INSTR
Next: LEFT