SQL LEFT() is used for extracting a substring starting from the left, followed by the number of characters. SQL LEFT() function extracts several characters from the string (starting from left).
SQL Left Function
SQL 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:
- String: An input string from which the substring will be extracted and can be a literal string, variable or a column.
- 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 | SQL Server And MySQL is over.
Recommended Posts
Understanding Difference Method in SQL
SQL CONCAT_WS Function Example