Text functions in SQL, also known as string functions, are functions that perform operations on string data types (such as CHAR, VARCHAR, and TEXT). These functions allow you to manipulate and query string values in various ways, such as concatenating strings, extracting substrings, converting case, replacing substrings, and more. Text functions are essential for handling and transforming text data stored in databases.
Common Text Functions in SQL
Here are some common and important text functions in SQL, along with their descriptions:
1. CONCAT: Concatenates two or more strings into one string.
SELECT CONCAT('Hello', ' ', 'World');
Result: Hello World
2. SUBSTRING: Extracts a substring from a string, starting at a specified position and optionally extending for a specified length.
SELECT SUBSTRING('Hello World', 1, 5);
Result: Hello
3. LENGTH: Returns the length of a string.
SELECT LENGTH('Hello');
Result: 5
4. UPPER: Converts all characters in a string to uppercase.
SELECT UPPER('hello');
Result: HELLO
5. LOWER: Converts all characters in a string to lowercase.
SELECT LOWER('HELLO');
Result: hello
6. TRIM: Removes leading and trailing spaces from a string.
SELECT TRIM(' Hello ');
Result: Hello
7. LTRIM: Removes leading spaces from a string.
SELECT LTRIM(' Hello');
Result: Hello
8. RTRIM: Removes trailing spaces from a string.
SELECT RTRIM('Hello ');
Result: Hello
9. REPLACE: Replace all occurrences of a specified substring with another substring.
SELECT REPLACE('Hello World', 'World', 'MySQL');
Result: Hello MySQL
10. INSTR: Returns the position of the first occurrence of a substring within a string.
SELECT INSTR('Hello World', 'World');
Result: 7
11. LOCATE: Similar to INSTR, returns the position of the first occurrence of a substring within a string.
SELECT LOCATE('World', 'Hello World');
Result: 7
12. POSITION: Similar to INSTR and LOCATE, returns the position of the first occurrence of a substring within a string.
SELECT POSITION('World' IN 'Hello World');
Result: 7
13. LEFT: Returns the leftmost number of characters from a string.
SELECT LEFT('Hello World', 5);
Result: Hello
14. RIGHT: Returns the rightmost number of characters from a string.
SELECT RIGHT('Hello World', 5);
Result: World
15. LPAD: Pads the left side of a string with another string to a specified length.
SELECT LPAD('Hello', 10, '*');
Result: *****Hello
16. RPAD: Pads the right side of a string with another string to a specified length.
SELECT RPAD('Hello', 10, '*');
Result: Hello*****
17. REPEAT: Repeats a string a specified number of times.
SELECT REPEAT('Hello', 3);
Result: HelloHelloHello
18. REVERSE: Reverses the characters in a string.
SELECT REVERSE('Hello');
Result: olleH
19. SPACE: Returns a string consisting of a specified number of spaces.
SELECT CONCAT('Hello', SPACE(5), 'World');
Result: Hello World
20. ASCII: Returns the ASCII value of the first character of a string.
SELECT ASCII('A');
Result: 65
These string functions are useful for performing a wide range of text manipulation and transformation tasks in SQL, making it easier to handle and analyze string data stored in databases.