{"id":4645,"date":"2013-12-15T23:35:08","date_gmt":"2013-12-16T07:35:08","guid":{"rendered":"http:\/\/www.mysqltutorial.org\/?page_id=4645"},"modified":"2023-10-12T21:54:12","modified_gmt":"2023-10-13T04:54:12","slug":"mysql-left-function","status":"publish","type":"page","link":"https:\/\/www.mysqltutorial.org\/mysql-string-functions\/mysql-left-function\/","title":{"rendered":"MySQL LEFT() Function"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to use the MySQL LEFT()<\/code> function to return the left part of a string with a specified length.<\/p>\n\n\n\n

Introduction to MySQL LEFT function<\/h2>\n\n\n\n

In MySQL, the LEFT<\/code> function allows you to extract the left part of a string with a specified length.<\/p>\n\n\n\n

The following shows the syntax of the LEFT<\/code> function.<\/p>\n\n\n

LEFT(str,length);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The LEFT<\/code> function accepts two arguments:<\/p>\n\n\n\n
    \n
  1. The str<\/code> is the string that you want to extract the substring.<\/li>\n\n\n\n
  2. The length<\/code> is a positive integer<\/a> that specifies the number of characters that will be returned.<\/li>\n<\/ol>\n\n\n\n
    The LEFT<\/code> function returns the leftmost length<\/code> characters from the str<\/code> string. It returns a NULL<\/code> value if either str<\/code> or length<\/code> argument is NULL<\/code>.<\/p>\n\n\n\n
    If the length<\/code> is zero or negative, the LEFT<\/code> function returns an empty string. If the length<\/code> is greater than the length of the str<\/code> string, the LEFT<\/code> function returns the entire str<\/code> string.<\/p>\n\n\n\n
    Notice that the SUBSTRING<\/a> (or SUBSTR<\/a>) function also provides the same functionality as the LEFT<\/code> function.<\/p>\n\n\n\n
    MySQL LEFT() function examples<\/h2>\n\n\n\n
    Let’s take some examples of using the LEFT()<\/code> function<\/p>\n\n\n\n
    1) Using MySQL LEFT() function with literal strings example<\/h3>\n\n\n\n
    The following statement uses the LEFT<\/code> function to return the 5 leftmost characters of the string MySQL LEFT<\/code>.<\/p>\n\n\n
    SELECT<\/span> LEFT<\/span>('MySQL LEFT'<\/span>, 5<\/span>);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
    \"MySQL<\/figure>\n\n\n\n
    The following statement returns the entire string because the length exceeds the length of the string.<\/p>\n\n\n
    SELECT<\/span> LEFT<\/span>('MySQL LEFT'<\/span>, 9999<\/span>);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
    \"MySQL<\/figure>\n\n\n\n
    The following statements return an empty string because the length is zero or negative.<\/p>\n\n\n
    SELECT<\/span> LEFT<\/span>('MySQL LEFT'<\/span>, 0<\/span>);\nSELECT<\/span> LEFT<\/span>('MySQL LEFT'<\/span>, -2<\/span>);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
    \"MySQL<\/figure>\n\n\n\n
    The following statement returns a NULL<\/code> value because the length is NULL<\/code>:<\/p>\n\n\n
    SELECT<\/span> LEFT<\/span>('MySQL LEFT'<\/span>, NULL<\/span>);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
    \"MySQL<\/figure>\n\n\n\n
    2) Using MySQL LEFT() function with table data<\/h3>\n\n\n\n
    Let’s take a look at the products<\/code> table in the sample database<\/a>:<\/p>\n\n\n\n
    \"\"<\/figure>\n\n\n\n
    Suppose you want to display the product name and product description in a catalog. The product description is long, therefore, you want to take just the first 50 characters for displaying.<\/p>\n\n\n\n
    The following statement uses the LEFT()<\/code> function to return the first 50 characters of the product description.<\/p>\n\n\n
    SELECT<\/span> \n    productname, LEFT<\/span>(productDescription, 50<\/span>) summary\nFROM<\/span>\n    products;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
    \"MySQL<\/figure>\n\n\n\n
    The LEFT<\/code> function returns the first 50 characters. It does not care about words.<\/p>\n\n\n\n
    You want to get the first 50 characters without cutting the word in the middle. To achieve this, you use the following steps:<\/p>\n\n\n\n
    1) Take the leftmost 50 characters of the productDescription<\/code> column.<\/p>\n\n\n
    SELECT<\/span> \n    LEFT<\/span>(productdescription, 50<\/span>)\nFROM<\/span>\n    products;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
    2) Reverse the substring using the REVERSE<\/code> function.<\/p>\n\n\n
    SELECT<\/span> \n    REVERSE<\/span>(LEFT<\/span>(productdescription, 50<\/span>))\nFROM<\/span>\n    products;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
    3) Get the first space’s position in the reversed substring using the LOCATE<\/code> function.<\/p>\n\n\n
    SELECT<\/span> \n    LOCATE<\/span>(' '<\/span>,REVERSE<\/span>(LEFT<\/span>(productdescription, 50<\/span>))) first_space_pos\nFROM<\/span>\n    products;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
    4) Minus 1 from the position. In case you don’t find any space, keep the position zero.<\/p>\n\n\n
    SELECT<\/span>\n    IFNULL<\/span>(NULLIF<\/span>(LOCATE<\/span>(' '<\/span>, REVERSE<\/span>(LEFT<\/span>(productDescription, 50<\/span>))), 0<\/span>) - 1<\/span>, 0<\/span>)\nFROM<\/span>\n    products;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
    5) Minus the position from the 50, you got the position. Let’s call it as last_space_pos.<\/code><\/p>\n\n\n
    SELECT<\/span>\n\tproductDescription,\n\t(50<\/span> - IFNULL<\/span>(NULLIF<\/span>(LOCATE<\/span>(' '<\/span>, REVERSE<\/span>(LEFT<\/span>(productDescription, 50<\/span>))), 0<\/span>) - 1<\/span>, 0<\/span>)) last_space_pos\nFROM<\/span>\n    products;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
    6) Take the leftmost last_space_pos<\/code> characters of the product description.<\/p>\n\n\n
    SELECT<\/span> \n    productDescription, LEFT<\/span>(productDescription, last_space_pos)\nFROM<\/span>\n    (SELECT<\/span> \n        productDescription,\n            (50<\/span> - IFNULL<\/span>(NULLIF<\/span>(LOCATE<\/span>(' '<\/span>, REVERSE<\/span>(LEFT<\/span>(productDescription, 50<\/span>))), 0<\/span>) - 1<\/span>, 0<\/span>)) last_space_pos\n    FROM<\/span>\n        products) AS<\/span> t;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
    \"MySQL<\/figure>\n\n\n\n
    Summary<\/h2>\n\n\n\n
      \n
    • Use the MySQL LEFT()<\/code> function to return the left part of a string with a specified length.<\/li>\n<\/ul>\n
      \n\t
      \n\t\t
      Was this tutorial helpful? <\/div>\n\t\t
      \n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
      \n\t\t
      \n\t\t\t
      <\/div>\n\t\t\t\n\t\t\t
    Try It Out<\/a><\/p>\n\n\n\n
    Try It Out<\/a><\/p>\n\n\n\n
    Try It Out<\/a><\/p>\n\n\n\n
    Try It Out<\/a><\/p>\n\n\n\n
    Try It Out<\/a><\/p>\n\n\n\n
    Try It Out<\/a><\/p>\n\n\n\n
    Try It Out<\/a><\/p>\n\n\n\n
    Try It Out<\/a><\/p>\n\n\n\n
    Try It Out<\/a><\/p>\n\n\n\n
    Try It Out<\/a><\/p>\n\n\n\n