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:

1. ASCII()
2. CHAR_LENGTH()
3. CHARACTER_LENGTH()
4. CONCAT()
5. CONCAT_WS()
6. FIND_IN_SET()
7. FORMAT()
8. INSERT()
9. INSTR()
10. LCASE()
11. LEFT()
12. LOCATE()
13. LOWER()
14. LPAD()
15. LTRIM()
16. MID()
17. POSITION()
18. REPEAT()
19. REPLACE()
20. REVERSE()
21. RIGHT()
22. RPAD()
23. RTRIM()
24. SPACE()
25. STRCMP()
26. SUBSTR()
27. SUBSTRING()
28. SUBSTRING_INDEX()
29. UCASE()
30. 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:

The following CREATE statement creates the Faculty_Info table:

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

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

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:

Syntax2: This syntax uses ASCII with the string:

Syntax3: This syntax uses ASCII with the character:

Example of ASCII String function:

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

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

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:

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

Examples of CHAR_LENGTH String function:

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

Output:

10    

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

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

Output:

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:

Syntax2: This syntax uses CHARACTER_LENGTH() with the 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:

Output:

28  

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

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

Output:

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:

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

Examples of CONCAT string function:

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

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:

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

Output:

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:

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

Examples of CONCAT_WS String function:

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

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:

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

Output:

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:

Examples of FIND_IN_SET String function:

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

Output:

3 

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

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:

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

Examples of FORMAT String function:

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

Output:

95.80%   

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

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

Output:

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:

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

Examples of INSERT String function:

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

Output:

JavaTpointExcel   

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

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

Output:

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:

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

Examples of INSTR String function:

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

Output:

5  

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

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

Output:

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:

Syntax2: This syntax uses LCASE() with the 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.

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:

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

Output:

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:

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

Examples of LEFT String function:

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

Output:

The CAPITAL  

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

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

Output:

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:

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

Examples of LOCATE String function:

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

Output:

16   

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

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

Output:

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:

Syntax2: This syntax uses LOWER() with the 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.

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:

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

Output:

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:

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

Examples of LPAD String function:

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

Output:

###NEW  

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

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

Output:

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:

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

Examples of LTRIM String function:

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

Output:

IS THE CAPITAL OF INDIA   

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

Output:

'JAVATPOINT           '    

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

Output:

98221545   

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

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

Output:

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:

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

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.

Output:

DELHI IS T   

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

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

Output:

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:

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

Examples of POSITION String function:

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

Output:

25   

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

Output:

1    

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

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

Output:

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:

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

Examples of REPEAT String function:

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

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.

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:

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

Output:

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:

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

Examples of REPLACE String function:

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

Output:

Indian IT company   

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

Output:

IJKJKL    

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

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

Output:

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:

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

Examples of REVERSE String function:

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

Output:

tnioptavaj    

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

This SQL statement reverses the address of each faculty:

Output:

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:

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

Examples of RIGHT String function:

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

Output:

s NEW DELHI   

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

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

Output:

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:

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

Examples of RPAD String function:

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

Output:

NEW###   

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

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

Output:

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:

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

Examples of RTRIM String function:

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

Output:

NEW DELHI IS THE   

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

Output:

'              JAVATPOINT'   

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

Output:

98221545    

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

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

Output:

SPACE String Function

This string function adds the specified number of spaces.

Syntax of SPACE String Function:

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:

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

Examples of STRCMP String function:

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

Output:

-1   

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

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:

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

Output:

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:

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

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.

Output:

DELHI IS T  

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

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

Output:

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:

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

Examples of SUBSTRING String function:

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

Output:

D    

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

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

Output:

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:

Example of SUBSTRING_INDEX String function:

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

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:

Syntax2: This syntax uses UCASE() with the 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.

Output:

THE CAPITAL OF INDIA IS NEW DELHI'  

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

Output:

E   

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

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

Output:

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:

Syntax2: This syntax uses UPPER() with the 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.

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:

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

Output: