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.

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

SQL Tutorial

SQL Database

SQL Table

SQL Select

SQL Clause

SQL Order By

SQL Insert

SQL Update

SQL Delete

SQL Join

SQL Keys

Difference

SQL Injection