Esat Erkec
What does do the SQL LEFT function?

SQL LEFT function in queries

October 22, 2021 by

In this article, we will learn how to use SQL LEFT function with straightforward examples.

What is the SQL LEFT function?

Most of the time we need to work with the character data types in our queries and we benefit from the built-in string functions in SQL Server to do this. The LEFT function is one of the built-in string functions and returns the left part of the input character string according to the specified number of characters. Now we will make a very basic example and then we will take a glance at the arguments and other details of this string function. In the following example, we input the string parameter as ‘SAVE THE GREEN’ and we want to extract the first 4 characters from the left.

How to use LEFT function in SQL

As we can see the LEFT function takes only two parameters and then returns the first 4 characters of the string from the left.

What does do the SQL LEFT function?

Syntax

The syntax for the LEFT function is as follows:

LEFT(stringnumber_of_characters)

Arguments

  • string: The string expression that wants to be extracted
  • number_of_characters: The number of the characters that will be extracted from the left side of the string. This number must be a positive integer number

Return type:

  • The return type of the LEFT function is varchar when the string parameter is a non-Unicode character data type
  • The return type of the LEFT function is nvarchar when the string parameter is a Unicode character data type

How to use SQL LEFT function with a table column

We can use the LEFT function to extract the characters of a table column. The following example returns the 4 leftmost characters of the FirstName column of the Person table.

Usage details of the LEFT function to parse table column

How to use SQL LEFT function with literal strings

Literal strings are character expressions that are enclosed in single or double quotation marks. For example, the following LEFT function will return the most left 5 characters in the string expression.

Get a left part of the literal string in SQL

If the number_of_characters exceeds the character number of the string parameter the LEFT function will return all strings. For example ‘SAVE THE GREEN’ expression character number is 14 and when we set the number_of_characters parameter as 15 the function will return the whole string.

Parse the left part of the literal string in SQL

Now, we will try to pass a negative number to the LEFT function. In this case, the function will return an error.

Invalid parameter error in SQL

How to use SQL LEFT function with variables

Local variables are used to hold a single value in the specified data type. We can use the LEFT function to extract the assigned values to them. The following example shows the usage of the LEFT function with the variables.

Variables and LEFT function

We can insert the result of the LEFT function into a temporary table using the SELECT INTO statement.

How to use LEFT function with variables

The #TempList table columns data types will equal the return type of the LEFT function. To find the column data types, we can use the INFORMATION_SCHEMA.COLUMNS view.

SQL temporary tables data types

As seen above, the columns data types are automatically generated referencing the variable data types.

SQL LEFT function and query performance

SQL Server indexes help to improve the performance of the queries but due to badly written queries, but in some cases, the query optimizer cannot use indexes efficiently due to incorrectly written queries. Generally, the most common mistake is made to use scalar functions after the where clause. In these cases, SQL Server can not use the indexes efficiently and can not generate an optimum query plan. Now let’s consider the following query. This query fetches records from the Person table with ‘Kim’ as the first 3 leftmost characters of the FirstName column.

When we look at the execution plan of this query it performs a non-clustered index scan operation. The index scan operator performs to read all index pages to find the qualified rows. Therefore, the data engine reads the whole records of the Person table and then filters the row that meets the where criteria. This process is not an efficient method and causes excessive resource consumption.

Query performance and LEFT function

Another interesting point about this execution plan is related to Compute Scalar operator. This operator converts the LEFT function into the SUBSTRING function.

Execution plan and string functions

Now, we will change our query to remove the LEFT function and use the LIKE operator. This change will not affect the query result set.

At this time, the optimizer makes an index suggestion and this suggestion can be seen in the execution plan.

LIKE operator can improve query performance

We will create the index and re-execute the same query.

After creating the index the query that involves a LIKE operator performs an index seek operator. It means that it finds the qualified rows very quickly using the B-tree structure of the index. The index seek is the most effective operation than the index scan operator. Index seek is more efficient than index scan operation.

Execution plan index seek

Now, we will execute our first sample query that uses the LEFT function after the where clause.

LEFT function affects the query performance

As we can see, the query still performs a non-clustered index scan because of the missing usage of the LEFT function in the query. As we have learned, we can use the LIKE operator instead of the LEFT function after the where clause.

Conclusion

In this article, we have learned usage details of the SQL LEFT function. This function helps to extract the strings from the left side.

Esat Erkec
168 Views