Tutorial Gateway

SQL LEFT Function

by

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

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

SQL LEFT Function Syntax

The basic syntax of the SQL LEFT Function 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.

In this article we will show you, How to write LEFT Function in SQL Server with example. For this, We are going to 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.

T-SQL CODE

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 LEFT function to return five leftmost characters from the @Character_Expression variable. We also assigned new name to that result as ‘SQL LEFT’ 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'

SQL LEFT Function Example 2

The 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

T-SQL CODE

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 and then we will extract the left most characters up to @ symbol using LEFT Function

T-SQL CODE

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

Thank You for Visiting Our Blog

Placed Under: SQL