The SQL left function is a 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
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
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 SQL Server 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
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, this String Function 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
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