How to Use LIKE in SQL

In this article, we will learn how to use LIKE to the column in the table of SQL database.

What is Like?

The LIKE is a SQL operator used to search for a particular pattern in each row of the field. This operator is always used with the WHERE clause in the SQL statement.

The syntax for using the Like operator in Structured Query Language:

SELECT Column_Name_1, Column_Name_2, Column_Name_3, ......, Column_Name_N FROM Table_Name WHERE Column_Name LIKE Pattern;

In Structured Query Language, the LIKE operator is used in conjunction with the following two wildcard characters:

Percent Sign (%)
Underscore (_)

Percent Sign (%)

This sign or wildcard character compares any string with zero, one, or multiple characters.

Syntax of using Percent Sign with LIKE operator:

1. The following syntax matches all the strings which begin with the character 'M':

SELECT Column_Name_1, Column_Name_2, ......, Column_Name_N FROM Table_Name WHERE Column_Name LIKE 'M%' ;

2. The following syntax matches all the strings which end with the character 'M':

SELECT Column_Name_1, Column_Name_2, ......, Column_Name_N FROM Table_Name WHERE Column_Name LIKE 'M%' ;

3. The following syntax matches all the strings which contain the character 'M' at any position:

SELECT Column_Name_1, Column_Name_2, ......, Column_Name_N FROM Table_Name WHERE Column_Name LIKE '%M%' ;

4. The following syntax matches all the strings which begin with the character 'M' and end with character 'T':

SELECT Column_Name_1, Column_Name_2, ......, Column_Name_N FROM Table_Name WHERE Column_Name LIKE 'M%T' ;

Underscore (_)

This wildcard character in SQL compares any string with a single character.

Syntax of using Underscore Sign with LIKE operator:

1. The following syntax matches all the strings which contain only two characters:

SELECT Column_Name_1, Column_Name_2, ......, Column_Name_N FROM Table_Name WHERE Column_Name LIKE 'M_' ;

2. The following syntax matches all the strings which contain the character 'm' at the second position:

SELECT Column_Name_1, Column_Name_2, ......, Column_Name_N FROM Table_Name WHERE Column_Name LIKE '_m%' ;

3. The following syntax matches all the strings which contain at least 5 characters and begin with the character 'M':

SELECT Column_Name_1, Column_Name_2, ......, Column_Name_N FROM Table_Name WHERE Column_Name LIKE 'M_____%' ;

If you want to add the SQL LIKE operator to the column in the table, you have to follow the following steps in the given sequence:

Create a database in the system.
Create the table in the database and insert the data into the database.
View the inserted data
Use the LIKE operator to the column of the table.

Now, we are going to explain the above steps with an example:

Step 1: Create a Database

In the Structured Query Language, creating a database is the first step for storing the structured tables in the database.

Use the following SQL syntax to create a database:

CREATE DATABASE Database_Name;

Suppose you want to create a College database. For this, you have to type the following command in Structured Query Language:

CREATE DATABASECollege;

Step 2: Create a Table and Insert the data

Now, use the following SQL syntax for creating the table in your database:

CREATE TABLE table_name
(
column_Name_1 data type (size of the column_1),
column_Name_2 data type (size of the column_2),
column_Name_3 data type (size of the column_3),
...
column_Name_N data type (size of the column_1)
);

Suppose you want to create the Student table with five columns in the College database. For this, you have to write the following query in your application:

CREATE TABLE Student
(
Roll_No Int,
First_Name VARCHAR (20),
City VARCHAR (20),
Age Int,
Percentage Int,
Grade VARCHAR (10)
) ;

Now, you have to insert the data in the table using the following syntax:

INSERT INTO <Table_Name> VALUES (value_1, value_2, value_3, ...., value_N);

Use the following query to insert the record of multiple students in the Student table of the College database:

INSERT INTO Student VALUES (101, Akash, Delhi, 18, 89, A2),
(102, Bhavesh, Kanpur, 19, 93, A1),
(103, Yash, Delhi, 20, 89, A2),
(104, Bhavna, Delhi, 19, 78, B1),
(105, Yatin, Lucknow, 20, 75, B1),
(106, Ishika, Ghaziabad, 19, 51, C1),
(107, Vivek, Goa, 20, 62, B2);

Step 3: View the Inserted Data

After table creation and data insertion, you can view the inserted record of the Student table by typing the following query in your SQL application:

SELECT * FROM Student;

Roll_No	First_Name	City	Age	Percentage	Grade
101	Akash	Delhi	18	89	A2
102	Bhavesh	Kanpur	19	93	A1
103	Yash	Delhi	20	89	A2
104	Bhavna	Delhi	19	78	B1
105	Yatin	Lucknow	20	75	B1
106	Ishika	Ghaziabad	19	91	C1
107	Vivek	Goa	20	80	B2

Step 4: Use the Like operator to the column in the table

The following query shows the record of those students from the Student table whose First_Name starts with 'B' letter:

SELECT Roll_No, First_Name, Percentage, Grade FROM Student WHERE First_Name LIKE 'B%' ;

Output of above query:

Roll_No	First_Name	Percentage	Grade
102	Bhavesh	93	A1
104	Bhavna	78	B1

As shown in the above output, the table only contains the record of Bhavesh and Bhavna because their names begin with B letters.

The following query shows the record of those students from the Student table whose First_Name ends with the 'h' letter:

SELECT Roll_No, First_Name, Percentage, Grade FROM Student WHERE First_Name LIKE '%h' ;

Output of above query:

Roll_No	First_Name	Percentage	Grade
101	Akash	89	A2
102	Bhavesh	93	A1
103	Yash	89	A2

As shown in the above output, the table only contains the record of Akash, Bhavesh, and Yash students because their names end with the letter h.

The following query shows the record of those students from the given Student table whose First_Name contains the character 'a' in any position:

SELECT Roll_No, First_Name, Percentage, Grade FROM Student WHERE First_Name LIKE '%a%' ;

Output of above query:

Roll_No	First_Name	City	Age	Percentage	Grade
101	Akash	Delhi	18	89	A2
102	Bhavesh	Kanpur	19	93	A1
103	Yash	Delhi	20	89	A2
104	Bhavna	Delhi	19	78	B1
105	Yatin	Lucknow	20	75	B1
106	Ishika	Ghaziabad	19	91	C1

As shown in the SQL output, the table contains the record of all students except Vivek student because Vivek name does not contain the letter 'a' in any position.

The following query shows the record of those students from the Student table whose city name begins with the 'D' letter and ends with the 'I' letter:

SELECT Roll_No, First_Name, City, Percentage FROM Student WHERE City LIKE 'D%i' ;

Output of above query:

Roll_No	First_Name	City	Percentage
101	Akash	Delhi	89
103	Yash	Delhi	89
104	Bhavna	Delhi	78

As shown in the above SQL output, the table only contains the record of those students whose City is Delhi.

The following query shows the record of those students from the Student table whose Percentage start with the '7' digit:

SELECT Roll_No, First_Name, City, Percentage, Grade FROM Student WHERE Percentage LIKE '7_' ;

Output of above query:

Roll_No	First_Name	City	Age	Percentage	Grade
104	Bhavna	Delhi	19	78	B1
105	Yatin	Lucknow	20	75	B1

As shown in the above SQL output, the table only contains the record of those students whose marks is 78 and 75.

The following query shows the record of those students from the Student table whose First_Name contains 'a' at third position:

SELECT Roll_No, First_Name, City, Percentage, Grade FROM Student WHERE First_Name LIKE '__a%' ;

Output of above query:

Roll_No	First_Name	City	Percentage	Grade
101	Akash	Delhi	89	A2
102	Bhavesh	Kanpur	93	A1
104	Bhavna	Delhi	78	B1

As shown in the above output, the table only contains the record of those students whose First_Name contains the character 'a' at the third position.

SQL String Functions

In this article, you will learn about the various string functions of Structured Query Language in detail with examples.

What are String Functions in SQL?

SQL String functions are the predefined functions that allow the database users for string manipulation. These functions only accept, process, and give results of the string data type.

Following are the most important string functions in Structured Query Language:

ASCII()
CHAR_LENGTH()
CHARACTER_LENGTH()
CONCAT()
CONCAT_WS()
FIND_IN_SET()
FORMAT()
INSERT()
INSTR()
LCASE()
LEFT()
LOCATE()
LOWER()
LPAD()
LTRIM()
MID()
POSITION()
REPEAT()
REPLACE()
REVERSE()
RIGHT()
RPAD()
RTRIM()
SPACE()
STRCMP()
SUBSTR()
SUBSTRING()
SUBSTRING_INDEX()
UCASE()
UPPER()

Let's discuss each string function in brief with the SQL table.

Current Time 0:18

Duration 18:10

Loaded: 6.24%

Now, we create a new table in SQL, which helps to understand each string function. The syntax for creating a 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 Faculty_Info table:

CREATE TABLE Faculty_Info
(
Faculty_ID INT NOT NULL PRIMARY KEY,
Faculty_First_Name VARCHAR (100),
Faculty_Last_Name VARCHAR (100),
Faculty_Dept_Id INT NOT NULL,
Faculty_AddressVarchar(120),
Faculty_City Varchar (80),
Faculty_Salary INT
);

The following INSERT queries insert the records of college Faculties in the Faculty_Info table:

INSERT INTO Faculty_Info (Faculty_ID, Faculty_First_Name, Faculty_Last_NameFaculty_Dept_Id, Faculty_Address, Faculty_City, Faculty_Salary) VALUES (1001, Arush, Sharma, 4001, Aman Vihar, Delhi, 20000);
INSERT INTO Faculty_Info (Faculty_ID, Faculty_First_Name, Faculty_Last_NameFaculty_Dept_Id, Faculty_Address, Faculty_City, Faculty_Salary) VALUES (1002, Bulbul, Roy, 4002, Nirman Vihar, Delhi, 38000 );
INSERT INTO Faculty_Info (Faculty_ID, Faculty_First_Name, Faculty_Last_NameFaculty_Dept_Id, Faculty_Address, Faculty_City, Faculty_Salary) VALUES (1004, Saurabh, Sharma, 4001, Sector 128, Mumbai, 45000);
INSERT INTO Faculty_Info (Faculty_ID, Faculty_First_Name, Faculty_Last_NameFaculty_Dept_Id, Faculty_Address, Faculty_City, Faculty_Salary) VALUES (1005, Shivani, Singhania, 4001, Vivek Vihar, Kolkata, 42000);
INSERT INTO Faculty_Info (Faculty_ID, Faculty_First_Name, Faculty_Last_NameFaculty_Dept_Id, Faculty_Address, Faculty_City, Faculty_Salary) VALUES (1006, Avinash, Sharma, 4002, Sarvodya Calony, Delhi, 28000);
INSERT INTO Faculty_Info (Faculty_ID, Faculty_First_Name, Faculty_Last_NameFaculty_Dept_Id, Faculty_Address, Faculty_City, Faculty_Salary)VALUES (1007, Shyam, Besas, 4003, Krishna Nagar, Lucknow, 35000);

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

SELECT * FROM Faculty_Info;

Faculty_Id	Faculty_First_Name	Faculty_Last_Name	Faculty_Dept_Id	Faculty_Address	Faculty_City	Faculty_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

ASCII String Function

This function in SQL returns the ASCII value of the character in the output. It gives the ASCII value of the left-most character of the string.

Syntax of ASCII String Function:

Syntax1: This syntax uses ASCII with the table column:

SELECT ASCII(Column_Name) as ASCII_Name FROM Table_Name;

Syntax2: This syntax uses ASCII with the string:

SELECT ASCII(String);

Syntax3: This syntax uses ASCII with the character:

SELECT ASCII(Character);

Example of ASCII String function:

The following SELECT query uses ASCII code with the Faculty_City column of the above Faculty_Info table.

SELECT Faculty_City, ASCII(Faculty_City) AS ASCII_code_of_column FROM Faculty_Info;

This query shows the ASCII code of the first character of all cities of the Faculty_City column.

Faculty_City	ASCII_Code_of_column
Delhi	68
Delhi	68
Mumbai	77
Kolkata	75
Delhi	68
Lucknow	76

CHAR_LENGTH String Function

This string function returns the length of the specified word. It shows the number of characters from the word.

Syntax of CHAR_LENGTH String Function:

Syntax1: This syntax uses CHAR_LENGTH() with the table column:

SELECT CHAR_LENGTH(Column_Name) as Alias_Name FROM Table_Name;

Syntax2: This syntax uses CHAR_LENGTH() with the word:

SELECT CHAR_LENGTH(word);

Examples of CHAR_LENGTH String function:

Example 1: This example shows the number of characters of the JavaTpoint word:

SELECT CHAR_LENGTH('JavaTpoint');

Output:

Example 2: This example uses CHAR_LENGTH() with the Faculty_Last_Name column of the above Faculty_Info table.

SELECT Faculty_Last_Name, CHAR_LENGTH(Faculty_Last_Name) AS Length_of_Last_Namecolumn FROM Faculty_Info;

This query shows the total number of characters of the last name of each faculty.

Output:

Faculty_Last_Name	Length_of_Last_Namecolumn
Sharma	6
Roy	3
Roy	3
Singhania	9
Sharma	6
Besas	5

CHARACTER_LENGTH String Function

This string function returns the length of the given string. It shows the number of all characters and spaces from the sentence.

Syntax of CHARACTER_LENGTH String Function:

Syntax1: This syntax uses CHARACTER_LENGTH() with the table column:

SELECT CHARACTER_LENGTH(Column_Name) AS Alias_Name FROM Table_Name;

Syntax2: This syntax uses CHARACTER_LENGTH() with the string:

SELECT CHARACTER_LENGTH(String);

Examples of CHARACTER_LENGTH String function:

Example 1: The following SELECT query shows the total number of characters and spaces of the specified string:

SELECT CHARACTER_LENGTH('JavaTpoint is a good company');

Output:

Example 2: The following SELECT query uses CHARACTER_LENGTH() with the Faculty_Addresss column of the above Faculty_Info table.

SELECT Faculty_Address, CHARACTER_LENGTH(Faculty_Address) AS Length_of_Address_column FROM Faculty_Info;

This SQL statement shows the total number of characters and spaces of the address of each faculty.

Output:

Faculty_Address	Length_of_Address_column
Aman Vihar	10
Nirman Vihar	12
Sector 128	10
Vivek Vihar	11
Sarvodya Calony	15
Krishna Nagar	13

CONCAT String Function

This string function concatenates two strings or words and forms a new string in the result.

Syntax of CONCAT String Function:

Syntax1: This syntax uses CONCAT() with table columns:

SELECT CONCAT(Column_Name1, Column_Name2, ..... column_NameN) AS Alias_Name FROM Table_Name;

Syntax2: This syntax uses CONCAT() with multiple strings:

SELECT CONCAT(String_1, String_2, String_3, ...., String_N);

Examples of CONCAT string function:

Example 1: The following SELECT query appends the multiple strings into a single string:

SELECT CONCAT('JavaTpoint', ' is', ' a', ' good', ' company.');

Output:

JavaTpoint is a good company

Example 2: The following SELECT query uses CONCAT() with the Faculty_First_Name and Faculty_Last_Name columns of above Faculty_Info table:

SELECT Faculty_First_Name, Faculty_Last_Name CONCAT(Faculty_First_Name, Faculty_Last_Name) AS Append_First_LastName FROM Faculty_Info;

This SQL statement merges the first name and last name of each faculty as shown in the below table:

Output:

Faculty_First_Name	Faculty_Last_Name	Append_First_LastName
Arush	Sharma	Arush Sharma
Bulbul	Roy	Bulbul Roy
Saurabh	Roy	Saurabh Roy
Shivani	Singhania	Shivani Singhania
Avinash	Sharma	Avinash Sharma
Shyam	Besas	Shyam Besas

CONCAT_WS String Function

This string function concatenates multiple strings or words with the help of concatenating symbol. This function uses another parameter that denotes the concatenate symbol.

Syntax of CONCAT_WS String Function:

Syntax1: This syntax uses CONCAT_WS() with table columns:

SELECT CONCAT_WS( Concatenate_symbol, Column_Name1, Column_Name2, ..... column_NameN) AS Alias_Name FROM Table_Name;

Syntax2: This syntax uses CONCAT_WS() with multiple strings:

SELECT CONCAT_WS(Concatenate_symbol, String_1, String_2, String_3, ...., String_N);

Examples of CONCAT_WS String function:

Example 1: The following SELECT query appends the multiple strings using the plus (+) symbol:

SELECT CONCAT_WS('+', 'JavaTpoint', ' is', ' a', ' good', ' company');

Output:

JavaTpoint+is+a+good+company

Example 2: The following SELECT query uses CONCAT_WS() with the Faculty_First_Name and Faculty_Last_Name columns of the above Faculty_Info table:

SELECT Faculty_First_Name, Faculty_Last_Name CONCAT_WS('.', Faculty_First_Name, Faculty_Last_Name) AS Append_First_LastName FROM Faculty_Info;

This SQL statement merges the first name and last name of each faculty by the dot symbol.

Output:

Faculty_First_Name	Faculty_Last_Name	Append_First_LastName
Arush	Sharma	Arush.Sharma
Bulbul	Roy	Bulbul.Roy
Saurabh	Roy	Saurabh.Roy
Shivani	Singhania	Shivani.Singhania
Avinash	Sharma	Avinash.Sharma
Shyam	Besas	Shyam.Besas

FIND_IN_SET String Function

This string function allows you to find the position of the searched_string in the set of strings.

Syntax of FIND_IN_SET String Function:

SELECT FIND_IN_SET(Concatenate_symbol, String_1, String_2, String_3, ...., String_N);

Examples of FIND_IN_SET String function:

Example 1: The following SELECT query searches 'a' character from the given set of characters:

SELECT FIND_IN_SET('a', 'JavaTpoint, is, a, good, company');

Output:

Example2: The following SELECT query searches 'Delhi' string from the given set of strings:

SELECT FIND_IN_SET('Delhi', 'Mumbai, Goa, Banglore, Delhi, Kolkata, Chennai');

Output:

FORMAT String Function

This String function allows you to display the given string in the specified format.

Syntax of FORMAT String Function:

Syntax1: This syntax uses FORMAT() with table column:

SELECT FORMAT(Column_Name1, Format_String) AS Alias_Name FROM Table_Name;

Syntax2: This syntax uses FORMAT() with the string:

SELECT FORMAT(String_1, Format_String);

Examples of FORMAT String function:

Example 1: The following SELECT query displays the number in the percentage format:

SELECT FORMAT('0.958', 'Percent');

Output:

95.80%

Example 2: The following SELECT query uses FORMAT() with the Faculty_Salary column of the above Faculty_Info table:

SELECT Faculty_Salary, FORMAT(Faculty_Salary, 'C') AS Currency_Salary FROM Faculty_Info;

This SQL statement displays the salary of each faculty in the currency format.

Output:

Faculty_Salary	Currency_Salary
20000	$20000.00
38000	$38000.00
45000	$45000.00
42000	$42000.00
28000	$28000.00
35000	$35000.00

INSERT String Function

This string function allows the database users to insert the sub-string in the original string at the given index position.

Syntax of INSERT String Function:

Syntax1: This syntax uses INSERT() with the column of the SQL:

SELECT INSERT(Column_Name, Position, Number, String) AS Alias_Name FROM Table_Name;

Syntax2: This syntax uses INSERT() with the string:

SELECT INSERT(String_1, Position, Number, String_2);

Examples of INSERT String function:

Example 1: The following SELECT query inserts the 'Tpoint' string at the fifth position in the 'JavaExcel' string:

SELECT INSERT('JavaExcel', 5, 6, 'Tpoint');

Output:

JavaTpointExcel

Example 2: The following SELECT query uses INSERT() with the Faculty_City column of the above Faculty_Info table:

SELECT Faculty_City, INSERT(Faculty_City, 3, 4, 'Agra') AS Insert_Agra FROM Faculty_Info;

This SQL statement inserts the Agra string at the third position in the city of each faculty.

Output:

Faculty_City	Insert_Agra
Delhi	DeAgralhi
Delhi	DeAgralhi
Mumbai	MuAgrambai
Kolkata	KoAgralkata
Delhi	DeAgralhi
Lucknow	LuAgracknow

INSTR String Function

This string function returns the index value of the first occurrence of the given character in the string.

Syntax of INSTR String Function:

Syntax1: This syntax uses INSTR() with the column of the SQL:

SELECT INSTR(Column_Name, character) AS Alias_Name FROM Table_Name;

Syntax2: This syntax uses INSTR() with the string:

SELECT INSTR(String, character);

Examples of INSTR String function:

Example 1: The following SELECT query shows the index value of the 'T' character in the JavaTpoint string

SELECT INSTR('JavaTpoint', 'T');

Output:

Example 2: The following SELECT query uses INSTR() with the Faculty_Address column of the above Faculty_Info table:

SELECT Faculty_Address, INSTR(Faculty_Address, 'a') AS INSTR_Address FROM Faculty_Info;

This SQL statement converts the cities of all faculties into lower case letters.

Output:

Faculty_Address	LCASE_Address
Aman Vihar	3
Nirman Vihar	5
Sactor 128	2
Vivek Vihar	10
Sarvodya Calony	2
Krishna Nagar	7

LCASE String Function

This string function allows users to convert the specified string into lower case letters.

Syntax of LCASE String Function:

Syntax1: This syntax uses LCASE() with the column of the SQL table:

SELECT LCASE(Column_Name) AS Alias_Name FROM Table_Name;

Syntax2: This syntax uses LCASE() with the string:

SELECT LCASE(String);

Examples of LCASE String function:

Example 1: The following SELECT query converts the upper case letters of the given string into the lower case letters.

SELECT LCASE( 'The CAPITAL of INDIA is NEW DELHI');

Output:

the capital of india is new delhi

Example 2: The following SELECT query uses LCASE() with the Faculty_Address column of the above Faculty_Info table:

SELECT Faculty_Address, LCASE(Faculty_Address) AS LCASE_Address FROM Faculty_Info;

This SQL statement converts the cities of all faculties into lower case letters.

Output:

Faculty_Address	LCASE_Address
Aman Vihar	aman vihar
Nirman Vihar	nirman vihar
Sector 128	sector 128
Vivek Vihar	vivek vihar
Sarvodya Calony	sarvodya colony
Krishna Nagar	krishna nagar

LEFT String Function

This string function shows the leftmost characters from the given string. It reads the characters to the given index position.

Syntax of LEFT String Function:

Syntax1: This syntax uses LEFT() with the column of the SQL table:

SELECT LEFT(Column_Name, Index_position) AS Alias_Name FROM Table_Name;

Syntax2: This syntax uses LEFT() with the string:

SELECT LEFT(String, Index_position);

Examples of LEFT String function:

Example 1: The following SELECT query shows the 11 leftmost characters from the given string:

SELECT LEFT( 'The CAPITAL of INDIA is NEW DELHI', 11);

Output:

The CAPITAL

Example 2: The following SELECT query uses LEFT() with the Faculty_Address column of the above Faculty_Info table:

SELECT Faculty_Address, LEFT(Faculty_Address, 6) AS LEFT_Address FROM Faculty_Info;

This SQL statement shows the 6 leftmost characters from the address of all faculties

Output:

Faculty_Address	LEFT_Address
Aman Vihar	Aman V
Nirman Vihar	Nirman
Sector 128	Sector
Vivek Vihar	Vivek
Sarvodya Calony	Sarvod
Krishna Nagar	Krishn

LOCATE String Function

This string function shows the index value of the first occurrence of the word in the given string.

Syntax of LOCATE String Function:

Syntax1: This syntax uses LOCATE() with the column of the SQL table:

SELECT LOCATE( Search_string, Column_Name, Search_position) AS Alias_Name FROM Table_Name;

Syntax2: This syntax uses LOCATE() with the string:

SELECT LOCATE(Search_string, String Search_position);

Examples of LOCATE String function:

Example 1: The following SELECT query shows the index value of the INDIA word in the given sentence:

SELECT LOCATE('INDIA','The CAPITAL of INDIA is NEW DELHI ', 1);

Output:

Example 2: The following SELECT query uses LOCATE() with the Faculty_Address column of the above Faculty_Info table:

SELECT Faculty_Address, LOCATE(' r ', Faculty_Address, 1) AS LOCATE_r_Address FROM Faculty_Info;

This SQL statement shows the index value of 'r' in the address of each faculty.

Output:

Faculty_Address	LOCATE_r_Address
Aman Vihar	10
Nirman Vihar	3
Sector 128	6
Vivek Vihar	11
Sarvodya Calony	3
Krishna Nagar	2

LOWER String Function

This string function allows users to convert the specified string into lower case letters. This function is also the same as the LCASE() string function.

Syntax of LOWER String Function:

Syntax1: This syntax uses LOWER() with the column of the SQL table:

SELECT LOWER(Column_Name) AS Alias_Name FROM Table_Name;

Syntax2: This syntax uses LOWER() with the string:

SELECT LOWER(String);

Examples of LOWER String function:

Example 1: The following SELECT query converts the upper case letters of the given string into the lower case letters.

SELECT LOWER( 'NEW DELHI IS THE CAPITAL OF INDIA');

Output:

new delhi is the capital of india

Example 2: The following SELECT query uses LOWER() with the Faculty_Address column of the above Faculty_Info table:

SELECT Faculty_Address, LOWER(Faculty_Address) AS LOWER_Address FROM Faculty_Info;

This SQL statement converts the cities of all faculties into lower case letters.

Output:

Faculty_Address	LOWER_Address
Aman Vihar	aman vihar
Nirman Vihar	nirman vihar
Sector 128	sector 128
Vivek Vihar	vivek vihar
Sarvodya Calony	sarvodya colony
Krishna Nagar	krishna nagar

LPAD String Function

This string function adds the given symbol to the left of the given string.

Syntax of LPAD String Function:

Syntax1: This syntax uses LPAD() with the column of the SQL table:

SELECT LPAD(Column_Name, size, symbol) AS Alias_Name FROM Table_Name;

Syntax2: This syntax uses LPAD() with the string:

SELECT LPAD(String, size, symbol);

Examples of LPAD String function:

Example 1: The following SELECT query adds the # symbol three times to the left of the NEW string:

SELECT LPAD( 'NEW', 6, '#');

Output:

###NEW

Example 2: The following SELECT query uses LPAD() with the Faculty_City column of the above Faculty_Info table:

SELECT Faculty_City, LPAD(Faculty_City, 10, '*') AS LPAD_City FROM Faculty_Info;

This SQL statement adds the * (asterisk) symbol five times to the left of the city of all faculties:

Output:

Faculty_City	LPAD_City
Delhi	*****Delhi
Delhi	*****Delhi
Mumbai	****Mumbai
Kolkata	***Kolkata
Delhi	*****Delhi
Lucknow	***Lucknow

LTRIM String Function

This string function cuts the given character or string from the left of the given original string. It also removes the space from the left of the specified string.

Syntax of LTRIM String Function:

Syntax1: This syntax uses LTRIM() with the column of the SQL table:

SELECT LTRIM(Column_Name, string) AS Alias_Name FROM Table_Name;

Syntax2: This syntax uses LTRIM() with the string:

SELECT LTRIM(Original_String, trimmed_string );

Examples of LTRIM String function:

Example 1: The following SELECT query trims the NEW DELHI words from the specified string:

SELECT LTRIM( 'NEW DELHI IS THE CAPITAL OF INDIA', 'NEW DELHI');

Output:

IS THE CAPITAL OF INDIA

Example 2: The following SELECT query trims the space from the specified string:

SELECT LTRIM( '              JAVATPOINT           ');

Output:

'JAVATPOINT '

Example 3: The following SELECT query trims the given character from the left of specified string:

SELECT LTRIM( '####98221545', '#');

Output:

98221545

Example 4: The following SELECT query uses LTRIM() with the Faculty_Last_Name column of above Faculty_Info table:

SELECT Faculty_Last_Name, LTRIM(Faculty_Last_Name) AS LTRIM_LastName FROM Faculty_Info;

This SQL statement trims the space from the left of the last name of all faculties:

Output:

Faculty_Last_Name	LTRIM_LastName
Sharma	Sharma
Roy	Roy
Roy	Roy
Singhania	Singhania
Sharma	Sharma
Besas	Besas

MID String Function

This string function extracts the sub-string from the given position of the original string.

Syntax of MID String Function:

Syntax1: This syntax uses MID() with the column of the SQL table:

SELECT MID(Column_Name, Starting_Position, Length) AS Alias_Name FROM Table_Name;

Syntax2: This syntax uses MID() with the string:

SELECT MID(Original_String, Starting_Position, Length);

Examples of MID String function:

Example 1: The following SELECT query shows the character from the 5^th to the 10^th position of the string.

SELECT MID( 'NEW DELHI IS THE CAPITAL OF INDIA', 5, 10);

Output:

DELHI IS T

Example 2: The following SELECT query uses MID() with the Faculty_Address column of the above Faculty_Info table:

SELECT Faculty_Address, MID(Faculty_Address, 3, 8 ) AS MID_Address FROM Faculty_Info;

This SQL statement shows the character from the 3^rd position till the 8^th position of the address.

Output:

Faculty_Address	MID_Address
Aman Vihar	an Vihar
Nirman Vihar	rman Vih
Sector 128	ctor 128
Vivek Vihar	vek Viha
Sarvodya Calony	rvodya C
Krishna Nagar	ishna Na

POSITION String Function

This string function finds the position of the first occurrence of the given string in the main string.

Syntax of POSITION String Function:

Syntax1: This syntax uses POSITION() with the column of the SQL table:

SELECT POSITION(String IN Column_Name) AS Alias_Name FROM Table_Name;

Syntax2: This syntax uses POSITION() with the string:

SELECT POSITION(String IN Original_String);

Examples of POSITION String function:

Example 1: The following SELECT query finds the position of the IT Company string in the original string:

SELECT POSITION( 'IT Company' IN'javatpoint is an indian IT company');

Output:

Example 2: The following SELECT query finds the position of the 'H' string in the original string:

SELECT POSITION( 'H' IN'HINDUSTAN');

Output:

Example 3: The following SELECT query uses POSITION() with the Faculty_Address column of the above Faculty_Info table:

SELECT Faculty_Address, POSITION('a' IN Faculty_Address ) AS POSITION_a_IN Address FROM Faculty_Info;

This SQL statement finds the position of character 'a' in the address of each faculty:

Output:

Faculty_Address	POSITION_a_IN Address
Aman Vihar	3
Nirman Vihar	5
Sector 128	0
Vivek Vihar	10
Sarvodya Calony	2
Krishna Nagar	7

REPEAT String Function

This string function writes the given string or character till the given number of times.

Syntax of REPEAT String Function:

Syntax1: This syntax uses REPEAT() with the column of the SQL table:

SELECT REPEAT(Column_Name, Repetation_Number) AS Alias_Name FROM Table_Name;

Syntax2: This syntax uses REPEAT() with the string:

SELECT REPEAT( String, Repetation_Number);

Examples of REPEAT String function:

Example 1: The following SELECT query writes the given string three times in the output.

SELECT REPEAT( 'javatpoint is an indian IT company', 3);

Output:

javatpoint is an indian IT companyjavatpoint is an indian IT companyjavatpoint is an indian IT company

Example 2: The following SELECT query writes the given character five times in the output.

SELECT REPEAT( 'H ' , 5);

Output:

H H H H H

Example 3: The following SELECT query uses REPEAT() with the Faculty_Address column of the above Faculty_Info table:

SELECT Faculty_Address, REPEAT( Faculty_Address, 2 ) AS REPEAT_Address FROM Faculty_Info;

This SQL statement writes the address of each faulty two times in the Repeat_Address column.

Output:

Faculty_Address	REPEAT_Address
Aman Vihar	Aman ViharAman Vihar
Nirman Vihar	Nirman ViharNirman Vihar
Sector 128	Sector 128Sector 128
Vivek Vihar	Vivek ViharVivek Vihar
Sarvodya Calony	Sarvodya CalonySarvodya Calony
Krishna Nagar	Krishna NagarKrishna Nagar

REPLACE String Function

This string function cuts the given string by removing the given sub-string.

Syntax of REPLACE String Function:

Syntax1: This syntax uses REPLACE() with the column of the SQL table:

SELECT REPLACE(Column_Name, sub_string) AS Alias_Name FROM Table_Name;

Syntax2: This syntax uses REPLACE() with the string:

SELECT REPLACE( Original_String, sub_string);

Examples of REPLACE String function:

Example 1: The following SELECT query removes the 'javatpoint' word from the original string:

SELECT REPLACE( 'javatpoint Indian IT company javatpoint', 'javatpoint');

Output:

Indian IT company

Example 2: The following SELECT query removes the given character H from the string:

SELECT REPLACE( 'HIJHKHJKL' , 'H');

Output:

IJKJKL

Example 3: The following SELECT query uses REPLACE() with the Faculty_Address column of the above Faculty_Info table:

SELECT Faculty_Address, REPLACE( Faculty_Address, 'a' ) AS REPLACE_a_Address FROM Faculty_Info;

This SQL statement removes the character a from the address of each faulty:

Output:

Faculty_Address	REPLACE_a_Address
Aman Vihar	Amn Vihr
Nirman Vihar	Nirmn Vihr
Sector 128	Sector 128
Vivek Vihar	Vivek Vihr
Sarvodya Calony	Srvody Clony
Krishna Nagar	Krishn Ngr

REVERSE String Function

This string function of Structured query Language reverses all the characters of the string.

Syntax of REVERSE String Function:

Syntax1: This syntax uses REVERSE() with the column of the SQL table:

SELECT REVERSE(Column_Name) AS Alias_Name FROM Table_Name;

Syntax2: This syntax uses REVERSE() with the string:

SELECT REVERSE(String);

Examples of REVERSE String function:

Example 1: The following SELECT query reverses the characters of the JavaTpoint string:

SELECT REVERSE( 'javatpoint');

Output:

tnioptavaj

Example 3: The following SELECT query uses REVERSE() with the Faculty_Address column of the above Faculty_Info table:

SELECT Faculty_Address, REVERSE( Faculty_Address ) AS REVERSE_Address FROM Faculty_Info;

This SQL statement reverses the address of each faculty:

Output:

Faculty_Address	REVERSE_Address
Aman Vihar	rahiv nama
Nirman Vihar	rahiv namrin
Sector 128	821 rotces
Vivek Vihar	rahiv keviv
Sarvodya Calony	ynolac aydovras
Krishna Nagar	ragan anhsirk

RIGHT String Function

This string function shows the right-most characters from the given string. It reads the characters from the right side to the given index position.

Syntax of RIGHT String Function:

Syntax1: This syntax uses RIGHT() with the column of the SQL table:

SELECT RIGHT(Column_Name, Index_position) AS Alias_Name FROM Table_Name;

Syntax2: This syntax uses RIGHT() with the string:

SELECT RIGHT(String, Index_position);

Examples of RIGHT String function:

Example 1: The following SELECT query shows the 11 right-most characters from the given string:

SELECT RIGHT( 'The CAPITAL of INDIA is NEW DELHI', 11);

Output:

s NEW DELHI

Example 2: The following SELECT query uses RIGHT() with the Faculty_Address column of the above Faculty_Info table:

SELECT Faculty_Address, RIGHT(Faculty_Address, 7) AS RIGHT_Address FROM Faculty_Info;

This SQL statement shows the 7 right-most characters from the address of each faculty.

Output:

Faculty_Address	RIGHT_Address
Aman Vihar	n Vihar
Nirman Vihar	n Vihar
Sector 128	tor 128
Vivek Vihar	k Vihar
Sarvodya Calony	Calony
Krishna Nagar	a Nagar

RPAD String Function

This string function adds the given symbol to the right of the given string.

Syntax of RPAD String Function:

Syntax1: This syntax uses RPAD() with the column of the SQL table:

SELECT RPAD(Column_Name, size, symbol) AS Alias_Name FROM Table_Name;

Syntax2: This syntax uses RPAD() with the string:

SELECT RPAD(String, size, symbol);

Examples of RPAD String function:

Example 1: The following SELECT query adds the # symbol three times to the right of the NEW string:

SELECT RPAD( 'NEW', 6, '#');

Output:

NEW###

Example 2: The following SELECT query uses RPAD() with the Faculty_City column of the above Faculty_Info table:

SELECT Faculty_City, RPAD(Faculty_City, 10, '*') AS RPAD_City FROM Faculty_Info;

This SQL statement adds the * (asterisk) symbol to the right of the city of each faculty.

Output:

Faculty_City	RPAD_City
Delhi	Delhi*****
Delhi	Delhi*****
Mumbai	Mumbai****
Kolkata	Kolkata***
Delhi	Delhi*****
Lucknow	Lucknow***

RTRIM String Function

This string function cuts the given character or string from the right of the given original string. It also removes the space from the right of the specified string.

Syntax of RTRIM String Function:

Syntax1: This syntax uses RTRIM() with the column of the SQL table:

SELECT RTRIM(Column_Name) AS Alias_Name FROM Table_Name;

Syntax2: This syntax uses RTRIM() with the string:

SELECT RTRIM(Original_String);

Examples of RTRIM String function:

Example 1: The following SELECT query trims the NEW DELHI words from the specified string:

SELECT RTRIM( 'NEW DELHI IS THE CAPITAL OF INDIA', 'CAPITAL OF INDIA');

Output:

NEW DELHI IS THE

Example 2: The following SELECT query trims the space from the right of the specified string:

SELECT RTRIM( '              JAVATPOINT           ');

Output:

' JAVATPOINT'

Example 3: The following SELECT query trims the given character from the right of the specified string:

SELECT RTRIM( '98221545####', '#');

Output:

98221545

Example 4: The following SELECT query uses RTRIM() with the Faculty_Address column of the above Faculty_Info table:

SELECT Faculty_Address, RTRIM(Faculty_Address) AS rtrimaddress FROM Faculty_Info;

This SQL statement trims the space from the right of the address of each faculty:

Output:

Faculty_Address	rtrimaddress
Aman Vihar	Aman Vihar
Nirman Vihar	Nirman Vihar
Sector 128	Sector 128
Vivek Vihar	Vivek Vihar
Sarvodya Calony	Sarvodya Calony
Krishna Nagar	Krishna Nagar

SPACE String Function

This string function adds the specified number of spaces.

Syntax of SPACE String Function:

SELECT SPACE(Number);

Example of SPACE String function:

The following SELECT query adds the 11 spaces:

SELECT SPACE(11);

Output:

___________

STRCMP String Function

This string function compares the two specified strings with each other. This function returns 0 if both strings in SQL are similar, returns -1 if the first string is smaller than the second string, and returns 1 if the first string is bigger than the second string.

Syntax of STRCMP String Function:

Syntax1: This syntax uses STRCMP() with the columns of the SQL table:

SELECT STRCMP(Column_Name1, Column_Name2) AS Alias_Name FROM Table_Name;

Syntax2: This syntax uses STRCMP() with the two strings:

SELECT STRCMP(String1, String2);

Examples of STRCMP String function:

Example 1: The following SELECT query compares the 'INDIA' string with the 'JavaTpoint' string.

SELECT STRCMP( 'INDIA, 'JavaTpoint');

Output:

-1

Example 2: The following SELECT query compares the 'INDIA' string with the 'Point' string.

SELECT STRCMP( 'INDIA, 'Point');

Output:

Example 3: The following SELECT query uses STRCMP() with the Faculty_first_Name and Faculty_Last_Name columns of the above Faculty_Info table:

SELECT Faculty_First_Name, Faculty_Last_Name, STRCMP(Faculty_First_Name, Faculty_Last_Name) AS STRCMP_Name FROM Faculty_Info;

This SQL statement compares the first name and last name of each faculty.

Output:

Faculty_First_Name	Faculty_Last_Name	STRCMP_Name
Arush	Sharma	-1
Bulbul	Roy	1
Saurabh	Roy	1
Shivani	Singhania	-1
Avinash	Sharma	1
Shyam	Besas	0

SUBSTR String Function

This string function extracts the sub-string from the given position of the original string.

Syntax of SUBSTR String Function:

Syntax1: This syntax uses SUBSTR() with the column of the SQL table:

SELECT SUBSTR(Column_Name, Starting_Position, Length) AS Alias_Name FROM Table_Name;

Syntax2: This syntax uses SUBSTR() with the string:

SELECT SUBSTR(Original_String, Starting_Position, Length);

Examples of SUBSTR String function:

Example 1: The following SELECT query shows the character from the 5t^h to the 10^th position of the string.

SELECT SUBSTR( 'NEW DELHI IS THE CAPITAL OF INDIA', 5, 10);

Output:

DELHI IS T

Example 2: The following SELECT query uses SUBSTR() with the Faculty_Address column of the above Faculty_Info table:

SELECT Faculty_Address, SUBSTR(Faculty_Address, 3, 8 ) AS SUBSTR_Address FROM Faculty_Info;

This SQL statement shows the substring from the 3^rd position to the 8^th position of the address.

Output:

Faculty_Address	SUBSTR_Address
Aman Vihar	an Vihar
Nirman Vihar	rman Vih
Sector 128	ctor 128
Vivek Vihar	vek Viha
Sarvodya Calony	rvodya C
Krishna Nagar	ishna Na

SUBSTRING String Function

This string function shows the character of the given index value in the original string.

Syntax of SUBSTRING String Function:

Syntax1: This syntax uses SUBSTRING() with the column of the SQL table:

SELECT SUBSTRING(Column_Name, Index_Position, Starting_Position) AS Alias_Name FROM Table_Name;

Syntax2: This syntax uses SUBSTRING() with the string:

SELECT SUBSTRING(Original_String, Index_Position, Starting_Position);

Examples of SUBSTRING String function:

Example 1: The following SELECT query shows the character of the fifth position from the left side.

SELECT SUBSTRING('NEW DELHI IS THE CAPITAL OF INDIA', 5, 1);

Output:

Example 2: The following SELECT query uses SUBSTRING() with the Faculty_Address column of the above Faculty_Info table:

SELECT Faculty_Address, SUBSTRING(Faculty_Address, 3, 1 ) AS SUBSTRING_Address FROM Faculty_Info;

This SQL statement shows the character of the 3rd position from the left side of the address of each faculty.

Output:

Faculty_Address	SUBSTRING_Address
Aman Vihar	a
Nirman Vihar	r
Sector 128	c
Vivek Vihar	v
Sarvodya Calony	r
Krishna Nagar	i

SUBSTRING_INDEX String Function

This string function shows the substring before the given symbol in the original string.

Syntax of SUBSTRING_INDEX String Function:

This syntax uses SUBSTRING_INDEX() with the string:

SELECT SUBSTRING_INDEX(Original_String, symbol, Starting_Position);

Example of SUBSTRING_INDEX String function:

The following SELECT query shows the substring before the @ symbol:

SELECT SUBSTRING_INDEX( 'NEW DELHI@IS THE CAPITAL OF INDIA', @, 1);

Output:

NEW DELHI

UCASE String Function

This string function allows users to convert the specified string into upper case letters or capital letters.

Syntax of UCASE String Function:

Syntax1: This syntax uses UCASE() with the column of the SQL table:

SELECT UCASE(Column_Name) AS Alias_Name FROM Table_Name;

Syntax2: This syntax uses UCASE() with the string:

SELECT UCASE(String);

Examples of UCASE String function:

Example 1: The following SELECT query converts the lower case letters of a given string into the upper case letters.

SELECT UCASE( 'The CAPITAL of INDIA is NEW DELHI');

Output:

THE CAPITAL OF INDIA IS NEW DELHI'

Example 1: The following SELECT query converts the given small letter into a capital letter:

SELECT UCASE( 'e');

Output:

Example 2: The following SELECT query uses UCASE() with the Faculty_Address column of the above Faculty_Info table:

SELECT Faculty_Address, UCASE(Faculty_Address) AS UCASE_City FROM Faculty_Info;

This SQL statement converts the cities of all faculties into capital letters.

Output:

Faculty_Address	UCASE_Address
Aman Vihar	AMAN VIHAR
Nirman Vihar	NIRMAN VIHAR
Sector 128	SECTOR 128
Vivek Vihar	VIVEK VIHAR
Sarvodya Calony	SARVODYA CALONY
Krishna Nagar	KRISHNA NAGAR

UPPER String Function

This string function allows users to convert the specified string into the UPPER case letters. This function is also the same as the UCASE() string function.

Syntax of UPPER String Function:

Syntax1: This syntax uses UPPER() with the column of the SQL table:

SELECT UPPER(Column_Name) AS Alias_Name FROM Table_Name;

Syntax2: This syntax uses UPPER() with the string:

SELECT UPPER(String);

Examples of UPPER String function:

Example 1: The following SELECT query converts the LOWER caseletters of the given string into the UPPER case letters.

SELECT UPPER( 'new delhi is the capital of India');

Output:

NEW DELHI IS THE CAPITAL OF INDIA

Example 2: The following SELECT query uses UPPER() with the Faculty_Address column of the above Faculty_Info table:

SELECT Faculty_Address, UPPER(Faculty_Address) AS UPPER_Address FROM Faculty_Info;

This SQL statement converts the cities of all faculties into the UPPER case letters.

Output:

Faculty_Address	UPPER_Address
Aman Vihar	AMAN VIHAR
Nirman Vihar	NIRMAN VIHAR
Sector 128	SECTOR 128
Vivek Vihar	VIVEK VIHAR
Sarvodya Calony	SARVODYA CALONY
Krishna Nagar	KRISHNA NAGAR

LTRIM Function in SQL

This string function truncates the given character or sub-string from the left of the given original string. It also truncates the space from the left of the specified string.

Syntax of LTRIM String Function

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

SELECT LTRIM(Column_Name, string) AS Alias_Name FROM Table_Name;

In the syntax, we have to specify the name of that column on which LTRIM function is to be run.

Syntax2: This syntax uses the LTRIM function with the set of characters (string):

SELECT LTRIM(Original_String, trimmed_string);

Syntax3: This syntax uses LTRIM function with a single character:

SELECT LTRIM(Original_String, trimmed_character);

Examples of LTRIM String function

Example 1: The following SELECT query truncates the given space from the specified string according to the LTRIM function:

SELECT LTRIM( ' JAVATPOINT',' ');

Output:

'JAVATPOINT'

Example 2: The following SELECT query truncates the space from the specified string according to the LTRIM function:

SELECT LTRIM(  '              JAVATPOINT           ');

Output:

'JAVATPOINT '

Example 3: The following SELECT query trims the CAPITAL OF INDIA substring from the specified string:

SELECT LTRIM(  'NEW DELHI IS THE CAPITAL OF INDIA', 'NEW DELHI IS THE ');

Output:

CAPITAL OF INDIA

Example 4: The following SELECT query trims the given symbol from the specified string:

SELECT LTRIM( '####98221545###', '#');

Output:

98221545###

Example 5: The following SELECT query trims the given set of numbers from the specified string:

SELECT LTRIM( '2021JavaTpoint2021', '2021');

Output:

JavaTpoint2021

Example 6: The following SELECT query trims the given set of numbers from the specified string:

SELECT LTRIM( '202120212021JavaTpoint', '2021');

Output:

JavaTpoint

Example 7: The following SELECT query trims all the numbers from the left side of the string which are present in the trimmed string:

SELECT LTRIM( '90287JavaTpoint', '0123456789');

This command actually removes the individual occurrence of numbers of the trimmed string.

Output:

JavaTpoint

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

First, we have to create the new SQL table, which helps to understand the LTRIM string function. 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 Faculty_Info table:

CREATE TABLE Faculty_Info
(
Faculty_ID INT NOT NULL PRIMARY KEY,
Faculty_First_Name VARCHAR (100),
Faculty_Last_Name VARCHAR (100),
Faculty_Dept_Id INT NOT NULL,
Faculty_Address Varchar(120),
Faculty_City Varchar (80),
Faculty_Salary INT
);

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

INSERT INTO Faculty_Info (Faculty_ID, Faculty_First_Name, Faculty_Last_Name Faculty_Dept_Id, Faculty_Address, Faculty_City, Faculty_Salary) VALUES (1001, '       Arush       ', '      Sharma       ', 4001, '      Aman Vihar       ', '      Delhi', 20000);
INSERT INTO Faculty_Info (Faculty_ID, Faculty_First_Name, Faculty_Last_Name Faculty_Dept_Id, Faculty_Address, Faculty_City, Faculty_Salary) VALUES (1002, '      Bulbul       ', '      Roy       ', 4002, '      Nirman Vihar       ', '      Delhi', 38000 );
INSERT INTO Faculty_Info (Faculty_ID, Faculty_First_Name, Faculty_Last_Name Faculty_Dept_Id, Faculty_Address, Faculty_City, Faculty_Salary) VALUES (1004, '      Saurabh       ', '      Sharma       ', 4001, '      Sector 128       ', '      Mumbai', 45000);
INSERT INTO Faculty_Info (Faculty_ID, Faculty_First_Name, Faculty_Last_Name Faculty_Dept_Id, Faculty_Address, Faculty_City, Faculty_Salary) VALUES (1005, '      Shivani       ', '      Singhania       ', 4001, '      Vivek Vihar       ', '      Kolkata', 42000);
INSERT INTO Faculty_Info (Faculty_ID, Faculty_First_Name, Faculty_Last_Name Faculty_Dept_Id, Faculty_Address, Faculty_City, Faculty_Salary) VALUES (1006, '      Avinash       ', '      Sharma       ', 4002, '      Sarvodya Calony       ', '      Delhi', 28000);
INSERT INTO Faculty_Info (Faculty_ID, Faculty_First_Name, Faculty_Last_Name Faculty_Dept_Id, Faculty_Address, Faculty_City, Faculty_Salary)VALUES (1007, '      Shyam       ', '      Besas       ', 4003, '      Krishna Nagar       ', '      Lucknow', 35000);

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

SELECT * FROM Faculty_Info;

Faculty_Id	Faculty_First_Name	Faculty_Last_Name	Faculty_Dept_Id	Faculty_Address	Faculty_City	Faculty_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

The following SELECT query uses the LTRIM function with the Faculty_Last_Name column of the above Faculty_Info table:

SELECT Faculty_Last_Name, LTRIM(Faculty_Last_Name) AS LTRIM_LastName FROM Faculty_Info;

This SQL statement trims the space from the left of the last name of each faculty:

Output:

Faculty_Last_Name	LTRIM_LastName
' Sharma '	'Sharma '
' Roy '	'Roy '
' Roy '	'Roy '
' Singhania '	'Singhania '
' Sharma '	'Sharma '
' Besas '	'Besas '

The following SELECT query uses the LTRIM function with the Faculty_First_Name and Faculty_Address columns of those faculty whose faculty_Id is greater than 1002 in the above Faculty_Info table:

SELECT Faculty_Id, LTRIM(Faculty_First_Name), LTRIM(Faculty_Address) FROM Faculty_Info WHERE Faculty_Id >1002;

This SQL statement trims the space from the left of the first name and address of those faculties whose Id is greater than 1002.

Output:

Faculty_Id	LTRIM(Faculty_First_Name)	LTRIM(Faculty_Address)
1004	'Saurabh '	'Sector 128 '
1005	'Shivani '	'Vivek Vihar '
1006	'Avinash '	'Sarvodya Calony '
1007	'Shyam '	'Krishna Nagar '

The following SELECT query uses the UPPER function with the Faculty_Last_Name column of the above Faculty_Info table:

SELECT Faculty_Last_Name, UPPER(Faculty_Last_Name) AS UPPER_LastName FROM Faculty_Info;

This SQL statement converts the last name in upper case of each faculty of the above table.

Output:

Faculty_Last_Name	UPPER_LastName
sharma	SHARMA
sroy	ROY
sroy	ROY
ssinghania	SINGHANIA
ssharma	SHARMA
sbesas	BESAS

The following SELECT query uses the UPPER function with the Faculty_First_Name, Faculty_City, and Faculty_Address columns of those faculties whose faculty_Id is greater than 1002 in the above Faculty_Info table:

SELECT Faculty_Id, UPPER(Faculty_First_Name), UPPER(Faculty_Address), UPPER(Faculty_City) FROM Faculty_Info WHERE Faculty_Id >1002;

Output:

Faculty_Id	UPPER(Faculty_First_Name)	UPPER(Faculty_Address)	UPPER(Faculty_City)
1004	SAURABH	SECTOR 128	MUMBAI
1005	SHIVANI	VIVEK VIHAR	KOLKATA
1006	AVINASH	SARVODYA CALONY	DELHI
1007	SHYAM	KRISHNA NAGAR	LUCKNOW

RTRIM Function in SQL

This string function truncates the given character or sub-string from the right of the given original string. It also truncates the space from the right of the specified string.

Syntax of RTRIM String Function

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

SELECT RTRIM(Column_Name, string) AS Alias_Name FROM Table_Name;

In the syntax, we have to specify the name of that column on which the RTRIM function is to be run.

Syntax2: This syntax uses the RTRIM function with the set of characters (string):

SELECT RTRIM(Original_String, trimmed_string);

Syntax3: This syntax uses the RTRIM function with a single character:

SELECT RTRIM(Original_String, trimmed_character);

Examples of RTRIM String function

Example 1: The following SELECT query truncates the given space from the specified string according to the RTRIM function:

SELECT RTRIM( 'JAVATPOINT ',' ');

Output:

'JAVATPOINT'

Example 2: The following SELECT query truncates the space from the specified string according to the RTRIM function:

SELECT RTRIM(  '              JAVATPOINT           ');

Output:

' JAVATPOINT'

Example 3: The following SELECT query trims the CAPITAL OF INDIA sub-string from the specified string:

SELECT RTRIM(  'NEW DELHI IS THE CAPITAL OF INDIA', 'CAPITAL OF INDIA');

Output:

NEW DELHI IS THE

Example 4: The following SELECT query trims the given symbol from the specified string:

SELECT RTRIM( '####98221545###', '#');

Output:

####98221545

Example 5: The following SELECT query trims the given set of numbers from the specified string:

SELECT RTRIM( '2021JavaTpoint2021', '2021');

Output:

2021JavaTpoint

Example 6: The following SELECT query trims the given set of numbers from the specified string:

SELECT RTRIM( 'JavaTpoint202120212021', '2021');

Output:

JavaTpoint

Example 7: The following SELECT query trims all the numbers from the right side of the string which are present in the trimmed string:

SELECT RTRIM( 'JavaTpoint90287', '0123456789');

This command removes the individual occurrence of numbers of the trimmed string.

Output:

JavaTpoint

Example 8: This example uses the RTRIM function with the table in Structured query Language.

In this example, we use the following Faculty_Info table, which helps to understand the LTRIM string function. 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 Faculty_Info table:

CREATE TABLE Faculty_Info
(
Faculty_ID INT NOT NULL PRIMARY KEY,
Faculty_First_Name VARCHAR (100),
Faculty_Last_Name VARCHAR (100),
Faculty_Dept_Id INT NOT NULL,
Faculty_Address Varchar(120),
Faculty_City Varchar (80),
Faculty_Salary INT
);

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

INSERT INTO Faculty_Info (Faculty_ID, Faculty_First_Name, Faculty_Last_Name Faculty_Dept_Id, Faculty_Address, Faculty_City, Faculty_Salary) VALUES (1001, 'Arush       ', 'Sharma       ', 4001, 'Aman Vihar       ', Delhi, 20000);
INSERT INTO Faculty_Info (Faculty_ID, Faculty_First_Name, Faculty_Last_Name Faculty_Dept_Id, Faculty_Address, Faculty_City, Faculty_Salary) VALUES (1002, 'Bulbul       ', 'Roy       ', 4002, 'Nirman Vihar       ', Delhi, 38000 );
INSERT INTO Faculty_Info (Faculty_ID, Faculty_First_Name, Faculty_Last_Name Faculty_Dept_Id, Faculty_Address, Faculty_City, Faculty_Salary) VALUES (1004, 'Saurabh       ', 'Sharma       ', 4001, 'Sector 128       ', Mumbai, 45000);
INSERT INTO Faculty_Info (Faculty_ID, Faculty_First_Name, Faculty_Last_Name Faculty_Dept_Id, Faculty_Address, Faculty_City, Faculty_Salary) VALUES (1005, 'Shivani       ', 'Singhania       ', 4001, 'Vivek Vihar       ', Kolkata, 42000);
INSERT INTO Faculty_Info (Faculty_ID, Faculty_First_Name, Faculty_Last_Name Faculty_Dept_Id, Faculty_Address, Faculty_City, Faculty_Salary) VALUES (1006, 'Avinash       ', 'Sharma       ', 4002, 'Sarvodya Calony       ', Delhi, 28000);
INSERT INTO Faculty_Info (Faculty_ID, Faculty_First_Name, Faculty_Last_Name Faculty_Dept_Id, Faculty_Address, Faculty_City, Faculty_Salary)VALUES (1007, 'Shyam       ', 'Besas       ', 4003, 'Krishna Nagar       ', Lucknow, 35000);

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

SELECT * FROM Faculty_Info;

Faculty_Id	Faculty_First_Name	Faculty_Last_Name	Faculty_Dept_Id	Faculty_Address	Faculty_City	Faculty_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