MySQL LEFT() function
LEFT() function
MySQL LEFT() returns a specified number of characters from the left of the string. Both the number and the string are supplied as arguments of the function.
This function is useful in -
- Substring extraction: It extracts characters from the left side of a string.
- Data truncation: The LEFT() function truncates or limits the length of a string by extracting only a certain number of characters.
- Data manipulation: LEFT() is commonly used to extract part of a string or modify its content based on the leftmost characters in a string.
Syntax:
LEFT (string, length)
Arguments:
| Name | Description |
|---|---|
| string | The string from which a number of characters from the left are to be returned. |
| length | An integer which indicates the number of characters to be returned starting from the left of the string in the first argument. |
Syntax Diagram:
MySQL Version: 8.0
Pictorial Presentation:
Example of MySQL LEFT() function
The following MySQL statement returns the pub_name and 5 characters from the left of pub_name from the publisher table.
Code:
SELECT pub_name, LEFT(pub_name, 5)
FROM publisher;
Sample table: publisher
+--------+------------------------------+-----------+-----------+----------------+--------------+------------+ | pub_id | pub_name | pub_city | country | country_office | no_of_branch | estd | +--------+------------------------------+-----------+-----------+----------------+--------------+------------+ | P001 | Jex Max Publication | New York | USA | New York | 15 | 1969-12-25 | | P002 | BPP Publication | Mumbai | India | New Delhi | 10 | 1985-10-01 | | P003 | New Harrold Publication | Adelaide | Australia | Sydney | 6 | 1975-09-05 | | P004 | Ultra Press Inc. | London | UK | London | 8 | 1948-07-10 | | P005 | Mountain Publication | Houstan | USA | Sun Diego | 25 | 1975-01-01 | | P006 | Summer Night Publication | New York | USA | Atlanta | 10 | 1990-12-10 | | P007 | Pieterson Grp. of Publishers | Cambridge | UK | London | 6 | 1950-07-15 | | P008 | Novel Publisher Ltd. | New Delhi | India | Bangalore | 10 | 2000-01-01 | +--------+------------------------------+-----------+-----------+----------------+--------------+------------+
Output:
mysql> SELECT pub_name, LEFT(pub_name, 5)
-> FROM publisher;
+------------------------------+-------------------+
| pub_name | LEFT(pub_name, 5) |
+------------------------------+-------------------+
| Jex Max Publication | Jex M |
| BPP Publication | BPP P |
| New Harrold Publication | New H |
| Ultra Press Inc. | Ultra |
| Mountain Publication | Mount |
| Summer Night Publication | Summe |
| Pieterson Grp. of Publishers | Piete |
| Novel Publisher Ltd. | Novel |
+------------------------------+-------------------+
8 rows in set (0.00 sec)
Example of MySQL LEFT() function with where clause
The following MySQL statement returns the pub_name(s) who belong to the USA and 5 characters from the left of those pub_name(s) from the publisher table.
Code:
SELECT pub_name,LEFT(pub_name,5)
FROM publisher
WHERE country='USA';
Sample table: publisher
+--------+------------------------------+-----------+-----------+----------------+--------------+------------+ | pub_id | pub_name | pub_city | country | country_office | no_of_branch | estd | +--------+------------------------------+-----------+-----------+----------------+--------------+------------+ | P001 | Jex Max Publication | New York | USA | New York | 15 | 1969-12-25 | | P002 | BPP Publication | Mumbai | India | New Delhi | 10 | 1985-10-01 | | P003 | New Harrold Publication | Adelaide | Australia | Sydney | 6 | 1975-09-05 | | P004 | Ultra Press Inc. | London | UK | London | 8 | 1948-07-10 | | P005 | Mountain Publication | Houstan | USA | Sun Diego | 25 | 1975-01-01 | | P006 | Summer Night Publication | New York | USA | Atlanta | 10 | 1990-12-10 | | P007 | Pieterson Grp. of Publishers | Cambridge | UK | London | 6 | 1950-07-15 | | P008 | Novel Publisher Ltd. | New Delhi | India | Bangalore | 10 | 2000-01-01 | +--------+------------------------------+-----------+-----------+----------------+--------------+------------+
Output:
mysql> SELECT pub_name,LEFT(pub_name,5)
-> FROM publisher
-> WHERE country='USA';
+--------------------------+------------------+
| pub_name | LEFT(pub_name,5) |
+--------------------------+------------------+
| Jex Max Publication | Jex M |
| Mountain Publication | Mount |
| Summer Night Publication | Summe |
+--------------------------+------------------+
3 rows in set (0.00 sec)
Video Presentation: