SQL LEFT Function

Summary: in this tutorial, you’ll learn how to use the SQL LEFT function to return a specified number of characters from the beginning of a string.

Introduction to the SQL LEFT Function #

In SQL, the LEFT function takes a string and returns a specified number of characters from the beginning of a string.

Here’s the syntax of the LEFT function:

LEFT(STRING, number_of_characters)Code language: SQL (Structured Query Language) (sql)

The LEFT function takes two parameters:

• string: The input string from which you want to return the character.
• number_of_characters: The number of characters you want to return from the input string.

The LEFT() function returns a string that contains a specified number of characters from the left of the input string.

The LEFT() function returns NULL if the input string or the number_of_characters is NULL.

Basic SQL LEFT function example #

The following statement uses the LEFT function to return the first three characters from the left of a string:

SELECT
  LEFT('SQL Tutorial', 3) result;Code language: SQL (Structured Query Language) (sql)

Try it

Output:

 result
--------
 SQLCode language: plaintext (plaintext)

Extracting initials of last names #

We’ll use the employees table from the HR sample database to demonstrate the LEFT function:

The following SELECT statement uses the LEFT function to return the initials of last names of employees:

SELECT
  last_name,
  LEFT(last_name, 1) AS initial
FROM
  employees
ORDER BY
  last_name;Code language: SQL (Structured Query Language) (sql)

Try it

Output:

  last_name  | initial
-------------+---------
 Austin      | A
 Baer        | B
 Baida       | B
 Bell        | B
 Chen        | C
 Colmenares  | C
 De Haan     | D
...Code language: plaintext (plaintext)

Using the LEFT function in the WHERE clause #

The following statement uses the LEFT function in the WHERE clause to find the employees whose last names start with the letter "K":

SELECT
  employee_id,
  last_name
FROM
  employees
WHERE
  LEFT(last_name, 1) = 'K';Code language: SQL (Structured Query Language) (sql)

Try it

Output:

 employee_id | last_name
-------------+-----------
         100 | King
         101 | Kochhar
         115 | Khoo
         122 | Kaufling
...Code language: plaintext (plaintext)

Using the LEFT function with an aggregate function #

The following statement uses the LEFT function with the COUNT aggregate function:

SELECT
  LEFT(last_name, 1) initial,
  COUNT(*) COUNT
FROM
  employees
GROUP BY
  LEFT(last_name, 1)
ORDER BY
  initial;Code language: SQL (Structured Query Language) (sql)

Try it

Output:

 initial | count
---------+-------
 A       |     1
 B       |     3
 C       |     2
 D       |     1
 E       |     2
...Code language: plaintext (plaintext)

The query returns the initials and the number of employees whose last names match the initials.

Summary #

• Use the LEFT function to extract a specified number of characters from the beginning of a string.