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:
10
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:
28
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:
3
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:
4
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:
5
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:
16
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 5th to the 10th 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 3rd position till the 8th 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:
25
Example 2: The following SELECT query finds the position of the ‘H’ string in the original string:
SELECT POSITION( 'H' IN'HINDUSTAN');
Output:
1
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:
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:
0
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 5th to the 10th 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 3rd position to the 8th 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:
D
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:
E
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 |
Leave a Reply