{"id":87164,"date":"2021-03-20T09:00:29","date_gmt":"2021-03-20T03:30:29","guid":{"rendered":"https:\/\/data-flair.training\/blogs\/?p=87164"},"modified":"2021-03-12T19:38:47","modified_gmt":"2021-03-12T14:08:47","slug":"sql-string-functions","status":"publish","type":"post","link":"https:\/\/data-flair.training\/blogs\/sql-string-functions\/","title":{"rendered":"SQL String Functions with Syntax and Examples"},"content":{"rendered":"

In this tutorial, we will discover the string functions in SQL.<\/p>\n

The string is a collection of characters put to use for storing multiple characters. Most of the data present around us is a string like our name, our address, etc.<\/p>\n

We often require modification and access to the strings around us. To make this process easy, SQL has some built-in string functions.<\/p>\n

In this article, we will be understanding the string function concepts in detail.<\/p>\n

Some of the major string functions are converting the string into upper and lower case. And after this concatenating and checking if a certain data is present in the string.<\/p>\n

Let us now dive into the string functions.<\/p>\n

What are SQL String Functions?<\/h3>\n

The built-in functions which take an input string and return an output string are called string functions.<\/p>\n

Some of the important string functions are as follows:<\/p>\n

1. SQL CHAR_LENGTH()<\/h4>\n

Helps to calculate the length of a given string.(not containing spaces).
\nSyntax:\u00a0<\/strong><\/p>\n

Select char_length(col_name) as length_name from tableName;\r\n<\/pre>\n
Example: Let us use the CHAR_LENGTH function on the name_emp column.
\nQuery:\u00a0<\/strong><\/p>\n
SELECT CHAR_LENGTH(name) as length_name from dataflair;\r\n<\/pre>\n
Output:<\/strong><\/p>\n
\"SQL<\/a><\/p>\n
2. ASCII in SQL<\/h4>\n
Returns the ASCII value of the expression.
\nSyntax:\u00a0<\/strong><\/p>\n
Select ASCII(col_name) as ascii_name from tableName;<\/pre>\n
Example: Let us use the CHAR_LENGTH function on the name_emp column.
\nQuery:\u00a0<\/strong><\/p>\n
SELECT ASCII(name) as ascii_name from dataflair;\r\n<\/pre>\n
Output:<\/strong><\/p>\n
\"ASCII<\/a><\/p>\n
3. SQL CHARACTER_LENGTH()<\/h4>\n
Returns the length of the character.
\nSyntax:\u00a0<\/strong><\/p>\n
Select character_length(col_name) as length_name from tableName;\r\n<\/pre>\n
Example: Let us use the CHARACTER_LENGTH function on the name_emp column.
\nQuery:\u00a0<\/strong><\/p>\n
SELECT character_length(name) as len_name from dataflair;\r\n<\/pre>\n
Output:<\/strong><\/p>\n
\"SQL<\/a><\/p>\n
4. SQL CONCAT()<\/h4>\n
This function is beneficial when we need to concatenate or merge two or more strings or words.
\nSyntax:<\/strong><\/p>\n
Select CONCAT(string,col_name) as new_col from tableName;\r\n<\/pre>\n
Example: Let us use the concat() function on the name_emp column.
\nQuery:\u00a0<\/strong><\/p>\n
SELECT CONCAT('ER.',name) as modified_name from dataflair;\r\n<\/pre>\n
Output:<\/strong><\/p>\n
\"SQL<\/a><\/p>\n
5. SQL CONCAT_WS()<\/h4>\n
Helps to concatenate strings or expressions using a symbol
\nSyntax:\u00a0<\/strong><\/p>\n
Select CONCAT(string,col_name) as new_col from tableName;\r\n<\/pre>\n
Example: Let us use the concat() function on the name_emp column.
\nQuery:\u00a0<\/strong><\/p>\n
SELECT CONCAT_WS('_','ER.',name) as modified_name from dataflair;\r\n<\/pre>\n
Output:<\/strong><\/p>\n
\"SQL<\/a><\/p>\n
6. SQL UCASE()<\/h4>\n
Returns the given string in the Upper case.
\nSyntax:\u00a0<\/strong><\/p>\n
Select UCASE(string,col_name) as new_col from tableName;\r\n<\/pre>\n
Example: Let us use the UCASE() function on the name_emp column.
\nQuery:<\/strong><\/p>\n
SELECT UCASE(name) as Uname from dataflair;\r\n<\/pre>\n
Output:<\/strong><\/p>\n
\"SQL<\/a><\/p>\n
7. SQL LCASE()<\/h4>\n
Returns the given string in lower case.
\nSyntax:<\/strong><\/p>\n
Select LCASE(string,col_name) as new_col from tableName;\r\n<\/pre>\n
Example: Let us use the LCASE() function on the name_emp column.
\nQuery:\u00a0<\/strong><\/p>\n
SELECT LCASE(name) as Lname from dataflair;\r\n<\/pre>\n
Output:<\/strong><\/p>\n
\"SQL<\/a><\/p>\n
8. SQL FIND_IN_SET()<\/h4>\n
Helps to find the given value in the given set of values.
\nSyntax:\u00a0<\/strong><\/p>\n
SELECT FIND_IN_SET('value', 'value1,value2,value3,.....') as position;\r\n<\/pre>\n
Example: Let us now see the working of the FIND_IN_SET() function.
\nQuery:\u00a0<\/strong><\/p>\n
SELECT FIND_IN_SET('ria', 'amit,ria,deepak,shyam,gita') as position;\r\n<\/pre>\n
Output:<\/strong><\/p>\n
\"SQL<\/a><\/p>\n
9. SQL FORMAT()<\/h4>\n
Helps to format the given value in the desired form.
\nSyntax:\u00a0<\/strong><\/p>\n
Select Format(colName, format) as alias FROM tableName;\r\n<\/pre>\n
Example: Let us now see the working of FORMAT() function.
\nQuery:\u00a0<\/strong><\/p>\n
Select Format(experience, 4) as experience FROM dataflair;\r\n<\/pre>\n
Output:<\/strong><\/p>\n
\"SQL<\/a><\/p>\n
10. SQL INSTR()<\/h4>\n
It helps to find the location of a given pattern in our target string.
\nSyntax:\u00a0<\/strong><\/p>\n
Select INSTR(name, 'a') as pos_e FROM dataflair;\r\n<\/pre>\n
Example: Let us now find the position of \u2018a\u2019 in the names of dataflair employees.
\nQuery:\u00a0<\/strong><\/p>\n
Select INSTR(name, 'a') as pos_e FROM dataflair;\r\n<\/pre>\n
Output:<\/strong><\/p>\n
\"SQL<\/a><\/p>\n
11. SQL LEFT()<\/h4>\n
It is useful to find a substring from the left containing the given number of characters.
\nSyntax:\u00a0<\/strong><\/p>\n
SELECT LEFT('string', n);\r\n<\/pre>\n
Example: Let us now try to find a substring of length 3 from DataFlair.
\nQuery:\u00a0<\/strong><\/p>\n
SELECT LEFT('dataflair', 3) as sub_str;\r\n<\/pre>\n
Output:<\/strong><\/p>\n
\"SQL<\/a><\/p>\n
12. SQL LENGTH()<\/h4>\n
This function calculates the length of the string.
\nSyntax:\u00a0<\/strong><\/p>\n
SELECT LENGTH(string) as alias;\r\n<\/pre>\n
Example: Let us now try to find the length of string \u2018DataFlair\u2019.
\nQuery:\u00a0<\/strong><\/p>\n
SELECT LENGTH('DataFlair') AS len;\r\n<\/pre>\n
Output:<\/strong><\/p>\n
\"SQL<\/a><\/p>\n
13. SQL LOCATE()<\/h4>\n
It helps us to find the nth position of occurrence of any substring in a given string.
\nSyntax:\u00a0<\/strong><\/p>\n
SELECT LOCATE(pattern, string, occurrence) as alias;\r\n<\/pre>\n
Example: Let us now find the location of the second occurrence of \u2018a\u2019 in the names of our employees at DataFlair.
\nQuery:<\/strong><\/p>\n
SELECT LOCATE('a', name, 2) as a_loc FROM dataflair;\r\n<\/pre>\n
Output:<\/strong><\/p>\n
\"SQL<\/a><\/p>\n
14. SQL LOWER()<\/h4>\n
It converts the given string into a lowercase string.
\nSyntax:\u00a0<\/strong><\/p>\n
SELECT LOWER(String);\r\n<\/pre>\n
Example: Let us now see the working of LOWER() function.
\nQuery:\u00a0<\/strong><\/p>\n
SELECT LOWER('DataFlair') as lower_str;\r\n<\/pre>\n
Output:<\/strong><\/p>\n
\"SQL<\/a><\/p>\n
15. SQL LPAD()<\/h4>\n
It is beneficial to make the given string of the required size by appending them at the left with the given signal.
\nSyntax:\u00a0<\/strong><\/p>\n
SELECT LPAD(string, size, symbol) as alias;\r\n<\/pre>\n
Example: Let us convert the names of our employees at DataFlair to size 10 with the \u2018$\u2019 symbol.
\nQuery:\u00a0<\/strong><\/p>\n
SELECT LPAD(name, 10, '$') as new_name FROM dataflair;\r\n<\/pre>\n
Output:<\/strong><\/p>\n
\"SQL<\/a><\/p>\n
16. SQL LTRIM()<\/h4>\n
It removes trailing spaces from the string.
\nSyntax:\u00a0<\/strong><\/p>\n
SELECT LTRIM(string) as alias;\r\n<\/pre>\n
Example: Let us now see how to remove trailing spaces from the string.
\nQuery:\u00a0<\/strong><\/p>\n
SELECT LTRIM(name) as new_name FROM dataflair;\r\n<\/pre>\n
Output:<\/strong><\/p>\n
\"SQL<\/a><\/p>\n
17. SQL MID()<\/h4>\n
It comes in handy to find a pattern of a given size from the original string starting from the position provided.
\nSyntax:\u00a0<\/strong><\/p>\n
SELECT Mid(string,position,size) as alias;\r\n<\/pre>\n
Example: Let us now try to find the MID starting from index 3 of the name column.
\nQuery:\u00a0<\/strong><\/p>\n
SELECT Mid(name, 3, 2) as new_name FROM dataflair;\r\n<\/pre>\n
Output:<\/strong><\/p>\n
\"SQL<\/a><\/p>\n
18. SQL POSITION()<\/h4>\n
Helps to find the position of the pattern in the given string.
\nSyntax:\u00a0<\/strong><\/p>\n
SELECT POSITION(pattern IN string);<\/pre>\n
Example: Let us now see the use of the POSITION() function.
\nQuery:\u00a0<\/strong><\/p>\n
SELECT POSITION(\u2018Data\u2019 IN DataFlair);\r\n<\/pre>\n
Output:<\/strong><\/p>\n
\"SQL<\/a><\/p>\n
19. SQL REPEAT()<\/h4>\n
Allows us to repeat the given string n number of times.
\nSyntax:\u00a0<\/strong><\/p>\n
SELECT REPEAT(string, size) as alias;\r\n<\/pre>\n
Example: Let us now look at the use of REPEAT() function.
\nQuery:\u00a0<\/strong><\/p>\n
SELECT REPEAT('DataFlair', 5) as repeated_str;\r\n<\/pre>\n
Output:<\/strong><\/p>\n
\"SQL<\/a><\/p>\n
20. SQL REPLACE()<\/h4>\n
Helps to replace a given pattern from the original string with another string.
\nSyntax:\u00a0<\/strong><\/p>\n
SELECT REPLACE(string, target_pattern, replace_pattern) as alias;\r\n<\/pre>\n
Example: Let us replace all the occurrences of \u2018a\u2019 in the string DataFlair.
\nQuery:\u00a0<\/strong><\/p>\n
SELECT REPLACE(\"DataFlair\", \"a\", \"b\") as b_new;\r\n<\/pre>\n
Output:<\/strong><\/p>\n
\"SQL<\/a><\/p>\n
21. SQL REVERSE()<\/h4>\n
It helps to reverse the original string.
\nSyntax:\u00a0<\/strong><\/p>\n
SELECT REVERSE(string) as alias;\r\n<\/pre>\n
Example: Let us now see the working of REVERSE() function.
\nQuery:\u00a0<\/strong><\/p>\n
SELECT REVERSE('DataFlair') as rev;\r\n<\/pre>\n
Output:<\/strong><\/p>\n
\"SQL<\/a><\/p>\n
22. SQL RIGHT()<\/h4>\n
This function is helpful in finding the substring from the right of the original string.
\nSyntax:\u00a0<\/strong><\/p>\n
SELECT RIGHT(string, position) as alias;\r\n<\/pre>\n
Example: Let us now try to find the substring from DataFlair considering positioning from index 4.
\nQuery:\u00a0<\/strong><\/p>\n
SELECT RIGHT('DataFlair', 4) as subStr;\r\n<\/pre>\n
Output:<\/strong><\/p>\n
\"SQL<\/a><\/p>\n
23. SQL RPAD()<\/h4>\n
It increases the length of the original string by adding symbols at the right of it.
\nSyntax:\u00a0<\/strong><\/p>\n
RPAD(string, new_size,symbol) as alias;\r\n<\/pre>\n
Example: Let us make the names of employees of size 10 in the DataFlair database.
\nQuery:\u00a0<\/strong><\/p>\n
SELECT RPAD(name, 10,'$') as new_name FROM dataflair;\r\n<\/pre>\n
Output:<\/strong><\/p>\n
\"SQL<\/a><\/p>\n
24. SQL RTRIM()<\/h4>\n
It removes the space from the right of the original string.
\nSyntax:\u00a0<\/strong><\/p>\n
RTRIM(string) as alias;\r\n<\/pre>\n
Example: Let us now see how to use RTRIM() function.
\nQuery:<\/strong><\/p>\n
SELECT RTRIM(name) as new_name FROM dataflair;\r\n<\/pre>\n
Output:<\/strong><\/p>\n
\"SQL<\/a><\/p>\n
25. SQL SPACE()<\/h4>\n
Helps to create a given number of spaces in the form of a string.
\nSyntax:\u00a0<\/strong><\/p>\n
SELECT SPACE(number) alias;\r\n<\/pre>\n
Example: Let us now understand the working of SPACE() function.
\nQuery:\u00a0<\/strong><\/p>\n
SELECT SPACE(8) as new_name ;\r\n<\/pre>\n
Output:<\/strong><\/p>\n
\"SQL<\/a><\/p>\n
26. SQL STRCMP()<\/h4>\n
It allows us to compare two strings. It returns:<\/p>\n