SQL String Functions with Syntax and Examples

In this tutorial, we will discover the string functions in SQL.

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.

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

In this article, we will be understanding the string function concepts in detail.

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.

Let us now dive into the string functions.

What are SQL String Functions?

The built-in functions which take an input string and return an output string are called string functions.

Some of the important string functions are as follows:

1. SQL CHAR_LENGTH()

Helps to calculate the length of a given string.(not containing spaces).
Syntax: 

Select char_length(col_name) as length_name from tableName;

Example: Let us use the CHAR_LENGTH function on the name_emp column.
Query: 

SELECT CHAR_LENGTH(name) as length_name from dataflair;

Output:

2. ASCII in SQL

Returns the ASCII value of the expression.
Syntax: 

Select ASCII(col_name) as ascii_name from tableName;

Example: Let us use the CHAR_LENGTH function on the name_emp column.
Query: 

SELECT ASCII(name) as ascii_name from dataflair;

Output:

3. SQL CHARACTER_LENGTH()

Returns the length of the character.
Syntax: 

Select character_length(col_name) as length_name from tableName;

Example: Let us use the CHARACTER_LENGTH function on the name_emp column.
Query: 

SELECT character_length(name) as len_name from dataflair;

Output:

4. SQL CONCAT()

This function is beneficial when we need to concatenate or merge two or more strings or words.
Syntax:

Select CONCAT(string,col_name) as new_col from tableName;

Example: Let us use the concat() function on the name_emp column.
Query: 

SELECT CONCAT('ER.',name) as modified_name from dataflair;

Output:

5. SQL CONCAT_WS()

Helps to concatenate strings or expressions using a symbol
Syntax: 

Select CONCAT(string,col_name) as new_col from tableName;

Example: Let us use the concat() function on the name_emp column.
Query: 

SELECT CONCAT_WS('_','ER.',name) as modified_name from dataflair;

Output:

6. SQL UCASE()

Returns the given string in the Upper case.
Syntax: 

Select UCASE(string,col_name) as new_col from tableName;

Example: Let us use the UCASE() function on the name_emp column.
Query:

SELECT UCASE(name) as Uname from dataflair;

Output:

7. SQL LCASE()

Returns the given string in lower case.
Syntax:

Select LCASE(string,col_name) as new_col from tableName;

Example: Let us use the LCASE() function on the name_emp column.
Query: 

SELECT LCASE(name) as Lname from dataflair;

Output:

8. SQL FIND_IN_SET()

Helps to find the given value in the given set of values.
Syntax: 

SELECT FIND_IN_SET('value', 'value1,value2,value3,.....') as position;

Example: Let us now see the working of the FIND_IN_SET() function.
Query: 

SELECT FIND_IN_SET('ria', 'amit,ria,deepak,shyam,gita') as position;

Output:

9. SQL FORMAT()

Helps to format the given value in the desired form.
Syntax: 

Select Format(colName, format) as alias FROM tableName;

Example: Let us now see the working of FORMAT() function.
Query: 

Select Format(experience, 4) as experience FROM dataflair;

Output:

10. SQL INSTR()

It helps to find the location of a given pattern in our target string.
Syntax: 

Select INSTR(name, 'a') as pos_e FROM dataflair;

Example: Let us now find the position of ‘a’ in the names of dataflair employees.
Query: 

Select INSTR(name, 'a') as pos_e FROM dataflair;

Output:

11. SQL LEFT()

It is useful to find a substring from the left containing the given number of characters.
Syntax: 

SELECT LEFT('string', n);

Example: Let us now try to find a substring of length 3 from DataFlair.
Query: 

SELECT LEFT('dataflair', 3) as sub_str;

Output:

12. SQL LENGTH()

This function calculates the length of the string.
Syntax: 

SELECT LENGTH(string) as alias;

Example: Let us now try to find the length of string ‘DataFlair’.
Query: 

SELECT LENGTH('DataFlair') AS len;

Output:

13. SQL LOCATE()

It helps us to find the nth position of occurrence of any substring in a given string.
Syntax: 

SELECT LOCATE(pattern, string, occurrence) as alias;

Example: Let us now find the location of the second occurrence of ‘a’ in the names of our employees at DataFlair.
Query:

SELECT LOCATE('a', name, 2) as a_loc FROM dataflair;

Output:

14. SQL LOWER()

It converts the given string into a lowercase string.
Syntax: 

SELECT LOWER(String);

Example: Let us now see the working of LOWER() function.
Query: 

SELECT LOWER('DataFlair') as lower_str;

Output:

15. SQL LPAD()

It is beneficial to make the given string of the required size by appending them at the left with the given signal.
Syntax: 

SELECT LPAD(string, size, symbol) as alias;

Example: Let us convert the names of our employees at DataFlair to size 10 with the ‘$’ symbol.
Query: 

SELECT LPAD(name, 10, '$') as new_name FROM dataflair;

Output:

16. SQL LTRIM()

It removes trailing spaces from the string.
Syntax: 

SELECT LTRIM(string) as alias;

Example: Let us now see how to remove trailing spaces from the string.
Query: 

SELECT LTRIM(name) as new_name FROM dataflair;

Output:

17. SQL MID()

It comes in handy to find a pattern of a given size from the original string starting from the position provided.
Syntax: 

SELECT Mid(string,position,size) as alias;

Example: Let us now try to find the MID starting from index 3 of the name column.
Query: 

SELECT Mid(name, 3, 2) as new_name FROM dataflair;

Output:

18. SQL POSITION()

Helps to find the position of the pattern in the given string.
Syntax: 

SELECT POSITION(pattern IN string);

Example: Let us now see the use of the POSITION() function.
Query: 

SELECT POSITION(‘Data’ IN DataFlair);

Output:

19. SQL REPEAT()

Allows us to repeat the given string n number of times.
Syntax: 

SELECT REPEAT(string, size) as alias;

Example: Let us now look at the use of REPEAT() function.
Query: 

SELECT REPEAT('DataFlair', 5) as repeated_str;

Output:

20. SQL REPLACE()

Helps to replace a given pattern from the original string with another string.
Syntax: 

SELECT REPLACE(string, target_pattern, replace_pattern) as alias;

Example: Let us replace all the occurrences of ‘a’ in the string DataFlair.
Query: 

SELECT REPLACE("DataFlair", "a", "b") as b_new;

Output:

21. SQL REVERSE()

It helps to reverse the original string.
Syntax: 

SELECT REVERSE(string) as alias;

Example: Let us now see the working of REVERSE() function.
Query: 

SELECT REVERSE('DataFlair') as rev;

Output:

22. SQL RIGHT()

This function is helpful in finding the substring from the right of the original string.
Syntax: 

SELECT RIGHT(string, position) as alias;

Example: Let us now try to find the substring from DataFlair considering positioning from index 4.
Query: 

SELECT RIGHT('DataFlair', 4) as subStr;

Output:

23. SQL RPAD()

It increases the length of the original string by adding symbols at the right of it.
Syntax: 

RPAD(string, new_size,symbol) as alias;

Example: Let us make the names of employees of size 10 in the DataFlair database.
Query: 

SELECT RPAD(name, 10,'$') as new_name FROM dataflair;

Output:

24. SQL RTRIM()

It removes the space from the right of the original string.
Syntax: 

RTRIM(string) as alias;

Example: Let us now see how to use RTRIM() function.
Query:

SELECT RTRIM(name) as new_name FROM dataflair;

Output:

25. SQL SPACE()

Helps to create a given number of spaces in the form of a string.
Syntax: 

SELECT SPACE(number) alias;

Example: Let us now understand the working of SPACE() function.
Query: 

SELECT SPACE(8) as new_name ;

Output:

26. SQL STRCMP()

It allows us to compare two strings. It returns:

• 0 – if strings are not the same.
• -1 – if string1 is smaller than string2.
• 1 – if string1 is larger than string2.

Syntax: 

SELECT STRCMP(str1,str2) as alias;

Example: Let us compare the DataFlair and Employee string.
Query:

SELECT STRCMP('DataFlair', 'Employee') as comparison;

Output:

27. SQL SUBSTR()

Allows us to find the substring from the given position of the original string.
Syntax: 

SELECT SUBSTR(string, position, size) as alias;

Example: Let us now find the substring of size 5 from DataFlair.
Query: 

SELECT SUBSTR('DataFlair', 1, 5) as substr;

Output:

28. SQL SUBSTRING()

Allows us to find a substring of provided length from the given position of the original substring.
Syntax: 

SELECT SUBSTRING(string, position, size) as alias;

Example: Let us now find the substring of size 1 from position 5 in the string DataFlair.
Query:

SELECT SUBSTRING('DataFlair', 5, 1) as sel_alpha;

Output:

29. SQL TRIM()

It removes a given pattern from the original string.
Syntax: 

SELECT TRIM(LEADING pattern FROM string);

Example: Let us now remove the leading ‘D’ from the string DataFlair.
Query: 

SELECT TRIM(LEADING 'D' FROM 'DataFlair');

Output:

30. SQL SUBSTRING_INDEX()

It helps us to find a substring in the original string before the given symbol.
Syntax: 

SELECT SUBSTRING_INDEX(string, pattern, 1) as new_str;

Example: Let us now see the working of SUBSTRING_INDEX() function.
Query: 

SELECT SUBSTRING_INDEX('DataFlair', 'F', 1) as new_str;

Output:

Summary

In this tutorial, we have discussed in detail the string functions available to us in SQL.

We have started from the syntax and went deep into the examples of each function.

We have understood all the methods available to us to capitalize or compare or find the substrings in the original string.