LEFT Function in SQL

The LEFT string function retrieves the characters to the given index value from the left side of the original string in the Structured Query Language.

Syntax of LEFT String Function

Syntax1: This syntax uses the LEFT function with the column name of the SQL table:

In the syntax, we have to specify the name of that column on which we want to perform the LEFT function.

Syntax3: This syntax uses the LEFT function with the string:

Examples of LEFT String function

Example 1: The following SELECT query shows the four characters with space from the left side of the specified word:

Output:

Left_4_characters

‘ JA’

Example 2: The following SELECT query shows the left 20 characters from the given string:

Output:

Left_20_characters

‘tutoraspire is a good’

Example 3: The following SELECT query shows the left 13 characters from the given sentence:

  SELECT LEFT( ‘NEW DELHI IS THE CAPITAL OF INDIA’, 13) AS Left_13_characters;;  

Output:

Left_20_characters

‘NEW DELHI IS’

Example 4: The following SELECT query shows the 5 characters from the left of the given string:

Output:

####9

Example 5: The following SELECT query shows the 100 characters from the left of the specified string:

Output:

Left_100_characters

String Functions

Example 8: This example uses the LEFT function with the table in Structured Query Language.

To understand the LEFT function with SQL, we have to create the SQL table first using CREATE statement. The syntax for creating the new table in the SQL database is as follows:

  CREATE TABLE table_name  (  1st_Column Data Type (character_size of 1st Column),    2nd_Column Data Type (character_size of the 2nd column ),    3rd_Column Data Type (character_size of the 3rd column),    …      Nth_Column Data Type (character_size of the Nth column)  );    

The following CREATE statement creates the Worker_Info table:

  CREATE TABLE Worker_Info  (  Worker_ID INT NOT NULL PRIMARY KEY,    Worker_First_Name VARCHAR (100),    Worker_Last_Name VARCHAR (100),    Worker_Dept_Id INT NOT NULL,  Worker_Address Varchar(120),  Worker_City Varchar (80),  Worker_Salary INT   );  

The below INSERT queries insert the records of college Faculties in the Worker_Info table:

  INSERT INTO Worker_Info (Worker_ID, Worker_First_Name, Worker_Last_Name Worker_Dept_Id, Worker_Address, Worker_City, Worker_Salary) VALUES (1001, Arush, Sharma, 4001, Aman Vihar, Delhi, 20000);  INSERT INTO Worker_Info (Worker_ID, Worker_First_Name, Worker_Last_Name Worker_Dept_Id, Worker_Address, Worker_City, Worker_Salary) VALUES (1002, Bulbul, Roy, 4002, Nirman Vihar, Delhi, 38000 );  INSERT INTO Worker_Info (Worker_ID, Worker_First_Name, Worker_Last_Name Worker_Dept_Id, Worker_Address, Worker_City, Worker_Salary) VALUES (1004, Saurabh, Sharma, 4001, Sector 128, Mumbai, 45000);  INSERT INTO Worker_Info (Worker_ID, Worker_First_Name, Worker_Last_Name Worker_Dept_Id, Worker_Address, Worker_City, Worker_Salary) VALUES (1005, Shivani, Singhania, 4001, Vivek Vihar, Kolkata, 42000);  INSERT INTO Worker_Info (Worker_ID, Worker_First_Name, Worker_Last_Name Worker_Dept_Id, Worker_Address, Worker_City, Worker_Salary) VALUES (1006, Avinash, Sharma, 4002, Sarvodya Calony, Delhi, 28000);  INSERT INTO Worker_Info (Worker_ID, Worker_First_Name, Worker_Last_Name Worker_Dept_Id, Worker_Address, Worker_City, Worker_Salary)VALUES (1007, Shyam, Besas, 4003, Krishna Nagar, Lucknow, 35000);  

The following SELECT statement displays the inserted records of the above Worker_Info table:

Worker_Id	Worker_First_Name	Worker_Last_Name	Worker_Dept_Id	Worker_Address	Worker_City	Worker_Salary
1001	Arush	Sharma	4001	Aman Vihar	Delhi	20000
1002	Bulbul	Roy	4002	Nirman Vihar	Delhi	38000
1004	Saurabh	Roy	4001	Sector 128	Mumbai	45000
1005	Shivani	Singhania	4001	Vivek Vihar	Kolkata	42000
1006	Avinash	Sharma	4002	Sarvodya Calony	Delhi	28000
1007	Shyam	Besas	4003	Krishna Nagar	Lucknow	35000

Query 1: The following SELECT query uses the LEFT function with the Worker_First_Name column of the above Worker_Info table:

  SELECT Worker_First_Name, LEFT(Worker_First_Name, 3) AS LEFT_3FirstName FROM Worker_Info;  

This SQL statement shows the first three characters of the first name of each worker.

Output:

Worker_First_Name	LEFT_3FirstName
Arush	Aru
Bulbul	Bul
Saurabh	Sau
Shivani	Shi
Avinash	Avi
Shyam	Shy

Query 2: The following SELECT query uses the LEFT function with the Worker_Last_Name column of the above Worker_Info table:

  SELECT Worker_Last_Name, LEFT(Worker_Last_Name, 2) AS LEFT_2LastName FROM Worker_Info;  

This SQL statement shows the first three characters of the last name of each worker.

Output:

Worker_Last_Name	LEFT_2LastName
Sharma
Sh
Roy
Ro
Roy
Ro
Singhania
Si
Sharma
Sh
Besas	Be

Query 3: The following SELECT query uses the LEFT function with the Worker_Address column of the above Worker_Info table:

  SELECT Worker_Address, LEFT(Worker_Address, 4) AS LEFT_4Address FROM Worker_Info;  

This SQL statement shows the four characters from the left of the address of each worker.

Output:

Worker_Address	LEFT_4Address
Aman Vihar	Aman
Nirman Vihar	Nirm
Sector 128	Sect
Vivek Vihar	Vive
Sarvodya Calony	Sarv
Krishna Nagar	Kris

Query 4: The following SELECT query uses the LEFT function with the Worker_City column of the above Worker_Info table:

This SQL statement shows the first character of the city of each worker.

Output:

Worker_City	LEFT_1City
Delhi	D
Delhi	D
Mumbai	M
Kolkata	K
Delhi	D
Lucknow	L

The following SELECT query uses the LEFT function with the Worker_First_Name and Worker_Address column of the above Worker_Info table:

  SELECT Worker_First_Name, LEFT(Worker_First_Name, 2) AS LEFT_2character, Worker_Address, LEFT(Worker_Address, 6) AS LEFT_6character FROM Worker_Info WHERE Worker_Salary > 30000;  

Output:

Worker_First_Name	LEFT_2character	Worker_Address	LEFT_6character
Bulbul	Bu	Nirman Vihar	Nirman
Saurabh	Sa	Sector 128	Sector
Shivani	Sh	Vivek Vihar	Vivek
Shyam	Sh	Krishna Nagar	Krishn

Next TopicLOCATE Function in SQL

LEFT Function in SQL

LEFT Function in SQL

Syntax of LEFT String Function

Examples of LEFT String function

ATN2 Function in SQL

TAN Function in SQL

You may also like