By: Daniel Calbimonte
The LEFT function is used to provide the left part of a string according to a specified number of characters.
Syntax
LEFT(expression, NumberCharactersReturned)
Parameters
- expression - this is the value, number or expression that we want use to get the left part.
- NumberCharactersReturned - is the number of characters returned from the left.
Simple LEFT Example
Below is a simple example of the LEFT function returning the first 5 characters from the left.
SELECT LEFT('Hello world',5) OUTPUT
Example of LEFT Using Table Column
The next example is showing the first 2 characters of the column Firstname from the Person table.
SELECT LEFT([FirstName],2) as OUTPUT FROM [Person].[Person]
Example of LEFT Function Combined with CHARINDEX
A common use of the LEFT function is when we get the position of a character using the CHARINDEX function.
In this example, we will get the position of the colon and then get the values from the left before the colon.
DECLARE @string nvarchar(20) = 'Example:New'
DECLARE @position int = CHARINDEX(':','Example:New')-1
SELECT LEFT(@string,@position) as output
We will get the string "Example" which is the substring of "Example:New" before the colon.
Here is another example, where we look for the first space in the address line and return just the left portion before the space. This example uses the AdventureWorks sample database.
SELECT LEFT(AddressLine1, CHARINDEX(' ',AddressLine1)-1), AddressLine1
FROM [Person].[Address]
WHERE AddressLine1 like '% %'
Here are a few rows from the output.
Error Message when using LEFT function
If the NumberCharactersReturned is a negative number, you will receive the following error message.
Invalid length parameter passed to the left function.
The following example reproduces the error message.
SELECT LEFT('Hello world',-5) OUTPUT
To solve the problem, make sure that the NumberCharactersReturned parameter is positive.
SELECT LEFT('Hello world',5) OUTPUT
