Tutorial Gateway

SQL LEFT Function

by

The SQL left function is a SQL String Function that returns the leftmost characters from a given expression. The SQL Server left function uses its second argument to decide how many left characters it should return. The syntax of the SQL Server Left Function is

SELECT LEFT (Character_Expression, Value)
FROM [Source]
  • Character_Expression: LEFT Function will write the left most characters from this expression.
  • Value: How many characters you want to extract from the Character_Expression.

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

SQL LEFT FUNCTION

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

SQL LEFT Function Example 1

The LEFT function returns the specified number of leftmost characters from the given string. The following LEFT function query returns left most 3 and 20 characters.

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

We used the left function to return five leftmost characters from the @Character_Expression variable.

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

In the next line, We assigned 20 to the second argument, which is greater than the string length. So, the 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 the leftmost characters from the column values. In this example, we will 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.

Character before @ using LEFT Function Example 3

Let us find the index position of a @ symbol present in the Email column using the CHARINDEX function. Next, we will extract the left-most characters up to @ symbol using the 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:

The below statement returns the index position of the @ symbol. It suggests the CHARINDEX function finds the index position of @ symbol in every record.

CHARINDEX ('@', [Email])

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

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

Placed Under: SQL