SQL Left Function Example | LEFT() In SQL Server And MySQL

SQL Left Function Example | LEFT() In SQL Server And MySQL is today’s topic. LEFT FUNCTION in SQL is used for extracting a substring starting from the left, followed by the number of characters. The LEFT() function extracts several characters from the string (starting from left).

SQL Left Function

The LEFT() function extracts a given number of characters from the left side of a supplied string.

The syntax of the LEFT() function is as follows.

SELECT LEFT (string, no_of_characters);

See the following parameters.

PARAMETERS:

1. String: An input string from which the substring will be extracted and can be a literal string, variable or a column.
2. No_of_characters: Number of characters you wish to extract from the string.

The LEFT() function returns a value of VARCHAR when the input_string is non-Unicode character data type and returns an NVARCHAR if the input_string is a Unicode character data type.

Example of SQL LEFT()

Query 1

SELECT LEFT ('AppDividend.com', 11);

Output

AppDividend

Here 11 characters were displayed starting from the left-most part of the string.

Query 2

SELECT LEFT ('AppDividend.com', 3);

Output

App

Here 3 characters were displayed starting from the left-most part of the string.

Query 3

SELECT LEFT ('SQL IS FUN', 4);

Output

'SQL '

Here 4 characters were displayed starting from the left-most part of the string. After SQL there is whitespace which is also counted as a character.

Query 4

SELECT LEFT ('SQL IS FUN', 20);

Output

SQL IS FUN

Here the number of characters exceeded the length of the string as a whole result string is displayed as an output.

Let’s apply the LEFT function in a Table.

Table: Employee

Emp_id	First_name	City	State	Phone
101	Rohit	Patna	Bihar	8585145852
201	Shivam	Jalandhar	Punjab	8958458785
301	Karan	Allahabad	Uttar Pradesh	9987845784
401	Suraj	Kolkata	West Bengal	8878989898
501	Akash	Vizag	Andhra Pradesh	9898547500

 

Now if we want to display the first 7 digits of the phone, then the following query has to be written:

Query 5

Select First_name AS Emp_Name, LEFT (Phone, 7) AS First_seven_digit 
from Employee;

Output

Emp_Name	Phone
Rohit	8585145
Shivam	8958458
Karan	9987845
Suraj	8878989
Akash	9898547

 

So, you can see that the first seven-digit of phone number is displayed under the column named Phone.

If the number_of_characters exceeds the length of the string, the LEFT function will return string.

MySQL LEFT() Function

MySQL LEFT() function extracts a number of characters from a string (starting from left).

Query

SELECT LEFT("MySQL Tutorial", 3) AS ExtractString;

Output

ExtractString
MyS

SQL Server LEFT() Function

The following statement uses LEFT() to return the three leftmost characters of the character string.

SELECT LEFT('Millie Bobby Brown', 6) Output_string;

Output

Output_string
-------------
Millie
 
(1 row affected)

Finally, SQL Left Function Example | LEFT() In SQL Server And MySQL is over.

Recommended Posts

Understanding Difference Method in SQL

SQL CONCAT_WS Function Example

SQL CHARINDEX Function Example

SQL Char Function Example

SQL ASCII FUNCTION Example