String functions in SQL are used to manipulate and operate on strings of text. These functions allow you to perform a variety of operations, such as finding lengths, extracting substrings, and modifying text. Below is a detailed guide on commonly used SQL string functions, including their syntax and examples.
Common SQL String Functions
| Function | Description | Syntax & Example |
|---|---|---|
ASCII() | Returns the ASCII value of the first character in a string. | SELECT ASCII('t');Output: 116 |
CHAR_LENGTH() | Returns the number of characters in a string. (Not available in SQL Server) | SELECT CHAR_LENGTH('Hello!');Output: 6 |
CHARACTER_LENGTH() | Returns the number of characters in a string. (Not available in SQL Server) | SELECT CHARACTER_LENGTH('geeks for geeks');Output: 15 |
CONCAT() | Concatenates two or more strings. | SELECT CONCAT('Geeks', ' ', 'forGeeks');Output: Geeks forGeeks |
CONCAT_WS() | Concatenates strings with a separator. | SELECT CONCAT_WS('_', 'geeks', 'for', 'geeks');Output: geeks_for_geeks |
FIND_IN_SET() | Finds the position of a string in a comma-separated list. | SELECT FIND_IN_SET('b', 'a, b, c, d, e, f');Output: 2 |
FORMAT() | Formats a number according to a specified format. | SELECT FORMAT(0.981, 'Percent');Output: 98.10% |
INSERT() | Inserts a new record into a database table. | INSERT INTO database (geek_id, geek_name) VALUES (5000, 'abc');Output: successfully updated |
INSTR() | Finds the position of a substring within a string. | SELECT INSTR('geeks for geeks', 'e');Output: 2 (first occurrence) |
SELECT INSTR('geeks for geeks', 'e', 1, 2);Output: 3 (second occurrence) | ||
LCASE() | Converts a string to lowercase. | SELECT LCASE('GeeksFor Geeks To Learn');Output: geeksforgeeks to learn |
LEFT() | Extracts a substring from the left side of a string. | SELECT LEFT('geeksforgeeks.org', 5);Output: geeks |
LENGTH() | Returns the length of a string. | SELECT LENGTH('GeeksForGeeks');Output: 13 |
LOCATE() | Finds the position of a substring in a string. | SELECT LOCATE('for', 'geeksforgeeks', 1);Output: 6 |
LOWER() | Converts a string to lowercase. | SELECT LOWER('GEEKSFORGEEKS.ORG');Output: geeksforgeeks.org |
LPAD() | Pads the left side of a string with a specified character. | SELECT LPAD('geeks', 8, '0');Output: 000geeks |
LTRIM() | Removes leading characters from a string. | SELECT LTRIM('123123geeks', '123');Output: geeks |
MID() | Extracts a substring from a string, starting at a specified position. | SELECT MID('geeksforgeeks', 6, 2);Output: for |
POSITION() | Finds the position of the first occurrence of a substring. | SELECT POSITION('e' IN 'geeksforgeeks');Output: 2 |
REPEAT() | Repeats a string a specified number of times. | SELECT REPEAT('geeks', 2);Output: geeksgeeks |
REPLACE() | Replaces occurrences of a substring with another substring. | SELECT REPLACE('123geeks123', '123');Output: geeks |
REVERSE() | Reverses the characters in a string. | SELECT REVERSE('geeksforgeeks.org');Output: gro.skeegrofskeeg |
RIGHT() | Extracts a substring from the right side of a string. | SELECT RIGHT('geeksforgeeks.org', 4);Output: .org |
RPAD() | Pads the right side of a string with a specified character. | SELECT RPAD('geeks', 8, '0');Output: geeks000 |
RTRIM() | Removes trailing characters from a string. | SELECT RTRIM('geeksxyxzyyy', 'xyz');Output: geeks |
SPACE() | Returns a string of a specified number of spaces. | SELECT SPACE(7);Output: (7 spaces) |
STRCMP() | Compares two strings and returns an integer indicating their relative order. | SELECT STRCMP('google.com', 'geeksforgeeks.com');Output: -1 |
SUBSTR() | Extracts a substring from a string starting at a specified position. | SELECT SUBSTR('geeksforgeeks', 1, 5);Output: geeks |
SUBSTRING() | Extracts a substring from a string starting at a specified position. | SELECT SUBSTRING('GeeksForGeeks.org', 9, 1);Output: G |
SUBSTRING_INDEX() | Extracts a substring before a specified delimiter. | SELECT SUBSTRING_INDEX('www.geeksforgeeks.org', '.', 1);Output: www |
TRIM() | Removes specified leading and trailing characters from a string. | SELECT TRIM(LEADING '0' FROM '000123');Output: 123 |
UCASE() | Converts a string to uppercase. | SELECT UCASE('GeeksForGeeks');Output: GEEKSFORGEEKS |
Conclusion
SQL string functions are essential for text manipulation and data formatting in databases. They allow for a wide range of operations, from simple text transformations to complex substring extractions. By mastering these functions, you can efficiently handle and analyze textual data within your SQL queries.