SQL Server LEFT() Function

Summary: in this tutorial, you will learn how to use the SQL Server LEFT() function to extract a specified number of characters from the beginning of a string.

SQL Server LEFT() function overview

The LEFT() function allows you to extract a specified number of characters from the left side of a string.

Here’s the syntax of the LEFT() function:

LEFT (string_expression, length ) Code language: SQL (Structured Query Language) (sql)

In this syntax:

• string_expression: This is the string you want to extract characters. The data type of the string_expression can be any data type that can be implicitly converted to VARCHAR or NVARCHAR except for TEXT or NTEXT.
• length: The number of characters to extract from the beginning of the string.

The LEFT() function returns a substring consisting of the specified number of characters from the left side of the input string. The LEFT() function will return NULL if either of the arguments is NULL.

SQL Server LEFT() function examples

Let’s take some examples of using the LEFT() function to understand it better.

1) Using the LEFT() function with a literal character string

The following statement uses LEFT() function to return the three leftmost characters of the character string SQL Server:

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

Here is the output:

result
-------------
SQL

(1 row affected)

2) Using the LEFT() function with a table data

We’ll use the production.products table from the sample database:

The following example uses the LEFT() function to return the seven leftmost characters of each product name in the production.products table:

SELECT 
  product_name, 
  LEFT(product_name, 7) first_7_characters 
FROM 
  production.products 
ORDER BY 
  product_name;Code language: SQL (Structured Query Language) (sql)

The following picture shows the partial output:

3) Using the LEFT() function with GROUP BY clause

The following example uses the LEFT() function to return a set of initials of the product name and the number of each product for each initial:

SELECT 
  LEFT(product_name, 1) initial, 
  COUNT(product_name) product_count 
FROM 
  production.products 
GROUP BY 
  left(product_name, 1) 
ORDER BY 
  initial;Code language: SQL (Structured Query Language) (sql)

Here is the output:

This query can be used for alphabetical pagination in applications.

Summary

• Use the LEFT() function to get the left part of a character string with the specified number of characters.