Introduction to SQL String Functions
SQL string functions are primarily utilized for string manipulation. The built-in SQL String functions make it easier for us to find and alter string values. For example, cutting blanks off a string value for display. You can use the LEN function to find the length of a string. It takes a single parameter containing a string expression, concatenating two strings. You can find the given the word from the sentence; even you can substring the character up to the given point in the string. You can find a word from the given point and of the given length using the MID function. You can also find the nth position of the given the word in a string.
Examples of String Functions in SQL
The string function is easy to use.
Here we will see how to use string function in SQL programming with the help of examples:
1. ASCII()
It gives you the ASCII value of a character.
Example:
Code:
SELECT ASCII('t');
Output:
116
2. CHAR_LENGTH()
It gives you the number of characters in the string.
Example:
Code:
SELECT CHAR_LENGTH('world!');
Output:
6
3. CHARACTER_LENGTH()
It gives you the number of characters in a given set of strings.
Example:
Code:
SELECT CHARACTER_LENGTH( 'educba.com' );
Output:
10
4. CONCAT()
It appends two strings to create the new single string, as shown in the example.
Example:
Code:
SELECT 'educba' || ' ' || '.com' FROM viber;
Output:
‘educba.com’
5. CONCAT_WS()
It appends two strings with a given symbol in between to concatenate them, as shown in the example.
Example:
Code:
SELECT CONCAT_WS('_', 'educba', 'to', 'learn');
Output:
educba_to_learn
6. FIND_IN_SET()
It finds out the index position of any symbol or character from the given set of characteristics.
Example:
Code:
SELECT FIND_IN_SET('v', 'z, x, v, b, n, m');
Output:
3
7. FORMAT()
It changes the format of the text from a string to any other format.
Example:
Code:
FORMAT("0.254", "Percent");
Output:
‘25.40%’
8. INSERT()
It helps you insert text, integer, float, or double into your database.
Example:
Code:
INSERT INTO mydb (name, age) VALUES (sdf, 20);
Output:
Inserted successfully
9. INSTR()
It gives you the first occurrence of the index of a character in the string.
Example:
Code:
INSTR('educba to learn', 'e');
Output:
1 (the first occurrence of ‘e’)
10. LCASE()
It will replace every character in the string in their lowercase letter.
Example:
Code:
LCASE("eduCBA.com To Learn");
Output:
educba.com to learn
11. LEFT()
It is used to get the substring from the left of the string to the given index position.
Example:
Code:
SELECT LEFT('educba.com', 3);
Output:
edu
12 LENGTH()
Gives you the length of the string.
Example:
Code:
LENGTH('educba.com');
Output:
10
13. LOCATE()
It gives you the position of a substring in the given string.
Example:
Code:
SELECT LOCATE('cba', 'educba.com', 1);
Output:
4
14. LOWER()
It converts every character in a string to lowercase from uppercase.
Code:
SELECT LOWER('EDUCBA.COM');
Output:
educba.com
15. LPAD()
It adds left padding with the given symbol to make the string of a given size.
Example:
Code:
LPAD('yahoo', 7, '@');
Output:
@@yahoo
16. LTRIM()
It trims the given character from the left of the string.
Example:
Code:
LTRIM('345yahoo', '345');
Output:
yahoo
17. MID()
It gives you the substring from a given position to the number of characters in the string.
Example:
Code:
MID("educba.com", 3, 2);
Output:
cba
18. POSITION()
It gives you the index position of the character in the string.
Example:
Code:
SELECT POSITION('u' IN 'educba.com');
Output:
3
19. REPEAT()
It repeats the string to the number of times given.
Example:
Code:
SELECT REPEAT('educba', 2);
Output:
educbaeducba
20. REPLACE()
It returns a new string by removing the given string from the original.
Example:
Code:
REPLACE('456yahoo456', '456');
Output:
yahoo
21. REVERSE()
It reverses the characters in a string.
Example:
Code:
SELECT REVERSE('educba.com');
Output:
moc.abcuda
22. RIGHT()
It is used to get the substring from the right of the string to the given index.
Example:
Code:
SELECT RIGHT('educba.com', 4);
Output:
‘.com’
23. RPAD()
It adds the right padding with the given symbol to make the string of the given size.
Example:
Code:
RPAD('educba', 7, '2');
Output:
‘educba@@’
24. RTRIM()
It trims the given character from the right of the string.
Example:
Code:
RTRIM('educbapou', 'pou');
Output:
‘educba’
25. SPACE()
It adds the number of spaces specified.
Example:
Code:
SELECT SPACE(6);
Output:
‘ ‘
26. STRCMP()
Matches two strings.
-
- If both the strings are the same, it gives 0.
- If the first is smaller than the second, then it gives -1.
- If the first is bigger than the second, then it gives 1.
Example:
Code:
SELECT STRCMP('zomato.com', 'educba.com');
Output:
0
27. SUBSTR()
It returns a new substring from a given position to the number of characters.
Example:
Code:
SUBSTR('educba.com', 1, 5);
Output:
‘educba’
28. SUBSTRING()
It gives you the substring character from the given string.
Example:
Code:
SELECT SUBSTRING('eduCba.com', 4, 1);
Output:
‘C’
29. SUBSTRING_INDEX()
It gives you the substring until it finds the given symbol.
Example:
Code:
SELECT SUBSTRING_INDEX( 'https://www.educba.com' , '/', 1);
Output:
‘https:’
30. TRIM()
It trims the given character from the string.
Example:
Code:
TRIM(LEADING '333' FROM '333567');
Output:
567
31. UCASE()
It will replace every character in the string in their uppercase letter.
Example:
Code:
UCASE("EduCbA");
Output:
EDUCBA
Conclusion
In the simplest mean, it is the user-defined function (UDF) in SQL server programming to help the user by accepting users’ input parameters and return the desired result as needed for them. You can send text field values as parameters in UDFs directly to manipulate the string. SQL string function is very useful. You can use SQL string functions to manipulate input string to concatenate, make lowercase, uppercase, increase the length of the string by adding specific character at end or starting, STRCMP string, compare two strings, get the type format of the input string, getting the desired results from the given set of text, finding the specific format of text from the string.
Recommended Articles
This has been a guide to SQL String Functions. Here we discussed how to use string function in SQL programming with the help of examples. You can also go through our other suggested articles to learn more–
