Tutorial Gateway

SQL LEFT Function

by

The SQL left function is one of the String Function, which is used to return left most characters from the specified expression. The Sql Server left function uses its second argument to decide, How many characters it should return.

TIP: The index position in Sql Server LEFT Function will start from 1, Not 0.

SQL LEFT Function Syntax

The basic syntax of the SQL LEFT in SQL Server is as shown below:

SELECT LEFT (Character_Expression, Value)
FROM [Source]
  • Character_Expression: Please specify the valid Expression on which to want to perform search operation. LEFT Function will write the left most characters from this expression.
  • Value: Please specify, how many characters you want to extract from the Character_Expression.

For this LEFT Function in SQL Server example, we use the below shown data

SQL LEFT FUNCTION

SQL LEFT Function Example 1

The LEFT Function is used to return the specified number of characters from the given string. The following query will show multiple ways to use LEFT function.

DECLARE @Charcater_Expression varchar(50)
SET @Charcater_Expression = 'Learn SQL Server' 

--Using Positive Integer 3 -- SQL
SELECT LEFT (@Charcater_Expression, 5) AS 'SQL LEFT' 

--Searching from specific position – 20
SELECT LEFT (@Charcater_Expression, 20) AS 'SQL LEFT'

OUTPUT

SQL LEFT FUNCTION 1

ANALYSIS

Below lines of code is used to declare string variable and assigning the string data.

DECLARE @Charcater_Expression varchar(50)
SET @Charcater_Expression = 'Learn SQL Server'

In below statement, We used Sql Server left function to return five leftmost characters from the @Character_Expression variable. We also assigned new name to that result using ALIAS Column.

--Using Positive Integer -- SQL
SELECT LEFT (@Charcater_Expression, 5) AS 'SQL LEFT'

In the next line, We set the second argument as 20, which is greater than the string length. So, LEFT function will return all the characters from the @Character_Expression

--Searching from specific position – 20
SELECT LEFT (@Charcater_Expression, 20) AS 'SQL LEFT'

LEFT Function Example 2

The Sql Server Left function also allows you to select left most characters from the column values. In this example, We are going to return the left four words of all the records present inside the Department Name column

-- Sql Server Left Function Example
SELECT [FirstName]
      ,[LastName]
      ,[DepartmentName]
      ,LEFT ([DepartmentName], 4) AS [SQL LEFT]
 FROM [Employe]

OUTPUT

SQL LEFT FUNCTION 2

NOTE: If you observe the second record, there is an Empty space after the Sr.

SQL LEFT Function Example 3

In this example, We are going to find the index position of a @ symbol present in Email column using CHARINDEX function. Next, we will extract the left most characters up to @ symbol using string left Function

-- Left function in Sql Server
SELECT [FirstName]
      ,[LastName]
      ,[DepartmentName]
      ,[Email]
      ,LEFT (
              [Email]
      ,CHARINDEX ('@', [Email]) - 1
            ) AS [SQL LEFT]
 FROM [Employe]

OUTPUT

SQL LEFT FUNCTION 3

ANALYSIS:

Below statement will return the index position of the @ symbol. It means, CHARINDEX function will check for the index position of @ symbol in each and every record.

CHARINDEX ('@', [Email])

We reduced the index position by 1, using the below statement. If you miss this, output include @ symbol as well

CHARINDEX ('@', [Email]) - 1

Placed Under: SQL