How LEFT works in SQL? Best LEFT examples

How LEFT works in SQL? Best LEFT examples

If you’ve ever worked with strings in SQL, you’ve probably come across various built-in functions that help manipulate data. One such function is LEFT(), which allows you to extract a specified number of characters from the beginning (or left side) of a string. In this article, I’m going to break down how the LEFT() function works, why it’s useful, and provide some of the best examples to illustrate its power.

Understanding the SQL LEFT() Function

The LEFT() function in SQL is used to extract a substring from a string, starting from the leftmost character. It takes two parameters:

  • The source string
  • The number of characters you want to extract

Its syntax looks like this:

LEFT(string, number_of_characters)

For example, if I want to extract the first three letters from the word “Database”, I would write:

SELECT LEFT('Database', 3);

This would return:

Dat

Why Use the LEFT() Function?

The LEFT() function is incredibly useful when dealing with data in SQL. Here are some common scenarios where you might need it:

  1. Extracting prefixes – Useful when working with standardized codes or identifiers.
  2. Formatting output – Helps create cleaner and more readable reports.
  3. Data cleaning – Used to remove unwanted text from strings.
  4. Manipulating text fields – Helps categorize or structure data properly.

LEFT() in Action

Let’s take a look at some practical examples where LEFT() can be used efficiently in different database scenarios.

Extracting Country Codes

Imagine you have a table with international phone numbers and you want to extract the country code:

SELECT phone_number, LEFT(phone_number, 3) AS country_code 
FROM contacts;

If your data looks like this:

phone_number
+15551234567
+442071231234

The result would be:

phone_number country_code
+15551234567 +15
+442071231234 +44

Using LEFT() with Table Data

Consider a products table where product IDs contain category prefixes:

SELECT product_id, product_name, LEFT(product_id, 4) AS category_code 
FROM products;

If the table holds data like this:

product_id product_name
ELEC1234 Smartphone
HOME5678 Vacuum Cleaner

The result would be:

product_id product_name category_code
ELEC1234 Smartphone ELEC
HOME5678 Vacuum Cleaner HOME

LEFT() Combined with Other SQL Functions

LEFT() can be used with other string functions in SQL to achieve even more robust data manipulations.

Combining LEFT() with UPPER()

If you want to extract characters from a string and convert them to uppercase, you can combine LEFT() with UPPER():

SELECT UPPER(LEFT('hello world', 5));

This will return:

HELLO

Combining LEFT() with CONCAT()

To format extracted data with additional context, you can combine LEFT() with CONCAT():

SELECT CONCAT(LEFT('product_code_123', 7), '...') AS shortened_text;

Result:

product...

Handling Edge Cases with LEFT()

While LEFT() is straightforward, there are a few things to keep in mind:

  • If the number of characters exceeds the string length, LEFT() returns the entire string.
  • If the source string is NULL, the function returns NULL.

Example:

SELECT LEFT('SQL', 10);

Returns:

SQL
SELECT LEFT(NULL, 3);

Returns:

NULL

Conclusion

The LEFT() function in SQL is a powerful tool for string manipulation, allowing you to extract specific portions of a text efficiently. Whether you’re dealing with phone numbers, product codes, or formatting strings for reports, LEFT() makes your SQL queries more effective. By combining it with other functions, you can customize your data further and ensure it meets your needs.

 

Other interesting article:

How CONCAT works in SQL? Best CONCAT examples