SQL LEFT()

The SQL LEFT() function extracts a specified number of characters from the left side of a given string. This function is particularly useful when you want to retrieve the starting characters of a string, such as extracting initials, codes, or parts of text fields.

The LEFT() function is widely supported in SQL databases, including SQL Server, MySQL, and PostgreSQL.

In this tutorial, we will go through SQL LEFt() String function, its syntax, and how to use this function in SQL statements for string operations, with the help of well detailed examples.

Syntax of SQL LEFT() Function

The basic syntax of the SQL LEFT() function is:

</>
Copy
LEFT(string, number_of_characters);

Each part of this syntax has a specific purpose:

  • string: The string from which you want to extract characters.
  • number_of_characters: The number of characters to extract from the left side of the string.

The LEFT() function returns the specified number of characters from the start of the string.

Setup for Examples: Creating the Database and Table

We’ll create a sample customers table with fields customer_name, customer_code, phone_number, and customer_id to demonstrate the LEFT() function examples.

1. First, create a new database called business:

</>
Copy
CREATE DATABASE business;

2. Select the business database to work with:

</>
Copy
USE business;

3. Create a table named customers with fields customer_id, customer_name, customer_code, and phone_number:

</>
Copy
CREATE TABLE customers (
    customer_id INT PRIMARY KEY AUTO_INCREMENT,
    customer_name VARCHAR(50),
    customer_code VARCHAR(10),
    phone_number VARCHAR(15)
);

4. Insert sample data into the customers table to use with the LEFT() function examples:

</>
Copy
INSERT INTO customers (customer_name, customer_code, phone_number)
VALUES 
('John Doe', 'CUST001', '1234567890'),
('Jane Smith', 'CUST002', '2345678901'),
('Alice Johnson', 'ALC003', '3456789012'),
('Bob Martin', 'CUST004', '4567890123');
SQL LEFT() - Setup database for examples

With this setup complete, you can run the LEFT() function examples to test and view results in the customers table.

Examples: Using LEFT() in SQL Queries

We’ll go through examples demonstrating the LEFT() function in SQL, using sample data from a customers table with fields customer_name and customer_code.

customers table data

1. Extracting Initials from Customer Names

To extract the first three characters of each customer_name:

</>
Copy
SELECT customer_name, LEFT(customer_name, 3) AS initials
FROM customers;

This query returns the first three characters of each customer’s name, which could be used as initials or a short code for the name.

SQL LEFT() - Example: Extracting Initials from Customer Names

2. Extracting Area Codes from Phone Numbers

To retrieve the first three digits of a phone number stored in phone_number, representing the area code:

</>
Copy
SELECT phone_number, LEFT(phone_number, 3) AS area_code
FROM customers;

This query returns the first three digits from each phone number, which could represent an area code if the number follows a standard format.

SQL LEFT() - Example: Extracting Area Codes from Phone Numbers

3. Using LEFT() with WHERE Clause

To find customers with codes that start with “CUST”:

</>
Copy
SELECT customer_name, customer_code
FROM customers
WHERE LEFT(customer_code, 4) = 'CUST';

This query filters for customer codes that begin with “CUST”, returning only customers whose codes match the specified prefix.

Reference: SQL WHERE

SQL LEFT() - Example: Using LEFT() with WHERE Clause

4. Using LEFT() to Format Output

To create a formatted output showing the first two letters of customer_name along with the customer ID:

</>
Copy
SELECT CONCAT(LEFT(customer_name, 2), '-', customer_id) AS formatted_code
FROM customers;

This query returns a formatted code like “Jo-1” for a customer named “John” with an ID of 1, using LEFT() for consistent code length.

SQL LEFT() - Example: Using LEFT() to Format Output

FAQs for SQL LEFT()

1. What does the SQL LEFT() function do?

The LEFT() function extracts a specified number of characters from the left side of a string.

2. Can LEFT() be used with numeric data?

No, LEFT() is designed for string data. For numeric data, consider converting the number to a string first.

3. What happens if number_of_characters exceeds the length of the string?

If number_of_characters is greater than the string length, LEFT() returns the entire string.

4. Is LEFT() supported in all SQL databases?

Yes, LEFT() is widely supported across SQL databases, including MySQL, SQL Server, PostgreSQL, and Oracle.

5. Can LEFT() be used with a dynamic number of characters?

Yes, the number_of_characters argument can be a variable or expression in SQL, allowing for dynamic extraction.