Tuesday 30 July 2019

How the LOWER() Function Works in MySQL

In MySQL, the LOWER() function converts uppercase characters to lowercase, according to the current character set mapping (the default mapping is utf8mb4).

Syntax

The syntax goes like this:
LOWER(str)
Where str is the string to be changed to lowercase.

Example

Here’s an example:
SELECT LOWER('CAT');
Result:
+--------------+
| LOWER('CAT') |
+--------------+
| cat          |
+--------------+
Of course, if the string already contains any lowercase characters, those characters will remain lowercase.
Example:
SELECT LOWER('Cat');
Result:
+--------------+
| LOWER('Cat') |
+--------------+
| cat          |
+--------------+

Database Example

Here’s an example of selecting data from a database and converting it to lowercase:
USE Music;
SELECT 
    ArtistName AS Original, 
    LOWER(ArtistName) AS Lowercase
FROM Artists
LIMIT 5;
Result:
+------------------+------------------+
| Original         | Lowercase        |
+------------------+------------------+
| Iron Maiden      | iron maiden      |
| AC/DC            | ac/dc            |
| Allan Holdsworth | allan holdsworth |
| Buddy Rich       | buddy rich       |
| Devin Townsend   | devin townsend   |
+------------------+------------------+

Binary Strings

This function doesn’t work on binary strings. If you need to use it on a binary string, you’ll need to convert it to a nonbinary string first. Here’s an example:
SET @str = BINARY 'Cat';
SELECT 
  LOWER(@str) AS 'Binary', 
  LOWER(CONVERT(@str USING utf8mb4)) AS 'Nonbinary';
Result:
+--------+-----------+
| Binary | Nonbinary |
+--------+-----------+
| Cat    | cat       |
+--------+-----------+

The LCASE() Function

The LCASE() function is a synonym for LOWER(). Note that if you use LCASE() within a view, it will be rewritten and stored as LOWER().

Convert to Uppercase

The UPPER() and UCASE() functions work the same way to convert characters to uppercase.

0 comments:

Post a Comment