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.
|
1 |
SELECT LEFT('SAVE THE GREEN',4) AS Result |
As we can see the LEFT function takes only two parameters and then returns the first 4 characters of the string from the left.
Syntax
The syntax for the LEFT function is as follows:
LEFT(string, number_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.
|
1 2 |
SELECT FirstName,LEFT(FirstName,3) AS NickName FROM Person.Person |
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.
|
1 |
SELECT LEFT('Peace will save the World',5) AS Result |
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.
|
1 |
SELECT LEFT('SAVE THE GREEN',15) AS Result |
Now, we will try to pass a negative number to the LEFT function. In this case, the function will return an error.
|
1 |
SELECT LEFT('SAVE THE GREEN',-1) AS Result |
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.
|
1 2 3 |
DECLARE @Txt AS VARCHAR(100) SET @Txt = 'Save Water Save Life' SELECT LEFT('Save Water Save Life',4) AS Result |
We can insert the result of the LEFT function into a temporary table using the SELECT INTO statement.
|
1 2 3 4 5 6 7 8 9 |
DECLARE @Txt AS VARCHAR(100) DECLARE @UnicodeTxt NCHAR(100) SET @Txt = 'Save Water Save Life' SET @UnicodeTxt= N'Green Earth' SELECT LEFT('Save Water Save Life',4) AS Result, LEFT(@UnicodeTxt,5) AS ResultUnicode INTO #TempList SELECT * FROM #TempList |
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.
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.
|
1 2 3 |
SELECT FirstName,LEFT(FirstName,3) AS NickName FROM Person.Person WHERE LEFT(FirstName,3)='Kim' |
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.
Another interesting point about this execution plan is related to Compute Scalar operator. This operator converts the LEFT function into the SUBSTRING function.
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.
|
1 2 3 |
SELECT FirstName,LEFT(FirstName,3) AS NickName FROM Person.Person WHERE 'Kim%' |
At this time, the optimizer makes an index suggestion and this suggestion can be seen in the execution plan.
We will create the index and re-execute the same query.
|
1 2 |
CREATE NONCLUSTERED INDEX [NewTestIndex_FirstName] ON [Person].[Person] ([FirstName]) |
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.
Now, we will execute our first sample query that uses the LEFT function after the where clause.
|
1 2 3 |
SELECT FirstName,LEFT(FirstName,3) AS NickName FROM Person.Person WHERE LEFT(FirstName,3)='Kim' |
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.
Most of his career has been focused on SQL Server Database Administration and Development. His current interests are in database administration and Business Intelligence. You can find him on LinkedIn.
View all posts by Esat Erkec
- SQL Performance Tuning tips for newbies - April 15, 2024
- SQL Unit Testing reference guide for beginners - August 11, 2023
- SQL Cheat Sheet for Newbies - February 21, 2023