SQL Server LEFT Function


By:

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
left query results

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]
left query results

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.

left query results

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.

left query results

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

Related Articles


Last Update: 11/4/2021




Comments For This Article





download














vote


get free sql tips
agree to terms