SQL - LEFT() Function

Quiz

The SQL LEFT() function is used to retrieve the leftmost length characters from the string.

It accepts a string value and a numerical value (say N) as a parameters and returns the specified string up to N characters from left to right. It returns NULL if the value of any of the given arguments is NULL.

If the given value is larger than the number of characters in the String, this function will return the actual String.

Syntax

Following is the syntax of the SQL LEFT() function −

LEFT(string, length);

Parameters

String − it is the value that will be provided as input to the LEFT function, and it can be any literal variable, string, or column
Length − Number of characters to be returned.

Example

Following is an example of the SQL LEFT() function −

SELECT LEFT('TUTORIALSPOINT COMPANY', 10) AS LeftFunction;

Output

Following is the output of the above query −

+--------------+
| LeftFunction |
+--------------+
| TUTORIALSP   |
+--------------+

Example

Following is another example of this function −

SELECT LEFT('Good Morning all, How are you?', 20) AS LeftFunction;

Output

The above SQL query produces the following output −

+----------------------+
| LeftFunction         |
+----------------------+
| Good Morning all, Ho |
+----------------------+

Example

If any of the argument passed to this function is NULL it returns NULL −

SELECT LEFT(NULL, 2) AS LEFTFunction;

Output

On executing the above query, it will produce the following output −

+--------------+
| LEFTFunction |
+--------------+
| NULL         |
+--------------+

Example

If we are giving string value and length in opposite order as length and string value, It will return the blank space −

SELECT LEFT(6, 'HELLOWORLD') AS LEFTFunction;

Output

On executing the above query, it will produce the following output −

+--------------+
| LEFTFunction |
+--------------+
|              |
+--------------+

Example

You can pass numerical values to this function −

SELECT LEFT(987654323456787, 8) AS LEFTFunction;

Output

On executing the above query, it will produce the following output −

+--------------+
| LEFTFunction |
+--------------+
| 98765432     |
+--------------+

Example

You can also pass symbol values to this function −

 SELECT LEFT('@#$%^&***^%$#@', 4) AS LEFTFunction;

Output

On executing the above query, it will produce the following output −

+--------------+
| LEFTFunction |
+--------------+
| @#$%         |
+--------------+

Example

You can pass the table column as an argument to the LEFT() function to convert the character or string into a LEFT Function. Assume we have created a table with the name Customers using the CREATE statement as follows −

create table CUSTOMERS(
   ID INT NOT NULL, 
   NAME VARCHAR(15) NOT NULL, 
   AGE INT NOT NULL, 
   ADDRESS CHAR(25), 
   SALARY DECIMAL(10, 4), PRIMARY KEY(ID)
);

Now let's insert seven records into the customers table using the INSERT statement as follows:−

insert INTO CUSTOMERS(ID, NAME, AGE, ADDRESS, SALARY) VALUES(1, 'Ramesh', 32, 'Ahmedabad', 2000.00);
insert INTO CUSTOMERS(ID, NAME, AGE, ADDRESS, SALARY) VALUES(2, 'Khilan', 25, 'Delhi', 1500.00);
insert INTO CUSTOMERS(ID, NAME, AGE, ADDRESS, SALARY) VALUES(3, 'kaushik', 23, 'Kota', 2000.00);
insert INTO CUSTOMERS(ID, NAME, AGE, ADDRESS, SALARY) VALUES(4, 'Chaitali', 25, 'Mumbai', 6500.00);
insert INTO CUSTOMERS(ID, NAME, AGE, ADDRESS, SALARY) VALUES(5, 'Hardik', 27, 'Bhopal', 8500.00);
insert INTO CUSTOMERS(ID, NAME, AGE, ADDRESS, SALARY) VALUES(6, 'Komal', 22, 'MP', 4500.00);
insert INTO CUSTOMERS(ID, NAME, AGE, ADDRESS, SALARY) VALUES(7, 'Muffy', 24, 'Indore', 10000.00);

The following SELECT query uses the LEFt function with the NAME column from the above CUSTOMERS table −

SELECT NAME, LEFT(NAME, 4) AS LEFTFunction FROM CUSTOMERS;

Output

After executing the above statement, it produces the following output −

+----------+--------------+
| NAME     | LEFTFunction |
+----------+--------------+
| Ramesh   | Rame         |
| Khilan   | Khil         |
| kaushik  | kaus         |
| Chaitali | Chai         |
| Hardik   | Hard         |
| Komal    | Koma         |
| Muffy    | Muff         |
+----------+--------------+

Example

The following SELECT query uses the LEFt function with the NAME, ADDRESS and SALARY column from the above CUSTOMERS table −

SELECT NAME, LEFT(NAME,5), ADDRESS,  LEFT(ADDRESS, 4), SALARY,  LEFT(SALARY, 3) FROM CUSTOMERS;

Output

After executing the above statement, it produces the following output −

+---------+-------------+----------+----------------+-----------+---------------+
| NAME    | LEFT(NAME,5)| ADDRESS  | LEFT(ADDRESS,4)|  SALARY   | LEFT(SALARY,3)|
+---------+-------------+----------+----------------+-----------+---------------+
| Ramesh  | Rames       | Ahmedabad| Ahme           | 2000.0000 | 200           |
| Khilan  | Khila       | Delhi    | Delh           | 1500.0000 | 150           |
| kaushik | kaush       | Kota     | Kota           | 2000.0000 | 200           |
| Chaitali| Chait       | Mumbai   | Mumb           | 6500.0000 | 650           |
| Hardik  | Hardi       | Bhopal   | Bhop           | 8500.0000 | 850           |
| Komal   | Komal       | MP       | MP             | 4500.0000 | 450           |
| Muffy   | Muffy       | Indore   | Indo           | 10000.0000| 100           |
+---------+-------------+----------+----------------+-----------+---------------+

sql-string-functions.htm

Print Page