String Functions in SQL
SQL provides a rich set of string functions to manipulate, search, format, and extract information from string data. These functions vary slightly by SQL dialect (e.g., SQL Server, MySQL, PostgreSQL), but many are shared across platforms.
? Common String Functions in SQL
| Function | Description | Example |
|---|---|---|
LEN() / LENGTH() | Returns the length of a string | LEN('Hello') ? 5 |
LEFT() | Returns the left part of a string | LEFT('Hello', 2) ? 'He' |
RIGHT() | Returns the right part of a string | RIGHT('Hello', 3) ? 'llo' |
SUBSTRING() / SUBSTR() | Extracts part of a string | SUBSTRING('Hello', 2, 3) ? 'ell' |
CHARINDEX() / INSTR() | Finds the position of a substring | CHARINDEX('e', 'Hello') ? 2 |
UPPER() / UCASE() | Converts to uppercase | UPPER('hello') ? 'HELLO' |
LOWER() / LCASE() | Converts to lowercase | LOWER('HELLO') ? 'hello' |
LTRIM() / TRIM(LEADING) | Removes leading spaces | LTRIM(' hello') ? 'hello' |
RTRIM() / TRIM(TRAILING) | Removes trailing spaces | RTRIM('hello ') ? 'hello' |
TRIM() | Removes both leading and trailing spaces | TRIM(' hello ') ? 'hello' |
REPLACE() | Replaces occurrences of a substring | REPLACE('SQL Tutorial', 'SQL', 'MySQL') ? 'MySQL Tutorial' |
REVERSE() | Reverses a string | REVERSE('abc') ? 'cba' |
CONCAT() / + | Concatenates two or more strings | CONCAT('Hello', 'World') ? 'HelloWorld' |
FORMAT() | Formats numbers or dates as strings | FORMAT(1234.56, 'N2') ? '1,234.56' |
STR() (SQL Server) | Converts numeric to string | STR(123.45, 6, 2) ? '123.45' |
SPACE(n) | Returns a string with n spaces | SPACE(3) ? ' ' |
REPLICATE() | Repeats a string n times | REPLICATE('A', 3) ? 'AAA' |
? Examples
-- Extract middle name from full nameSELECT SUBSTRING(FullName, 6, 4) AS MiddleNameFROM Employees;-- Clean and standardize dataSELECT TRIM(LOWER(UserName)) AS CleanedUserFROM Users;-- Concatenate first and last nameSELECT FirstName + ' ' + LastName AS FullNameFROM Employees;Note: Use
+for SQL Server,||for PostgreSQL, andCONCAT()for MySQL when concatenating strings.
? Dialect Differences
| Function | SQL Server | MySQL | PostgreSQL |
|---|---|---|---|
| Length | LEN() | LENGTH() | LENGTH() |
| Substring | SUBSTRING() | SUBSTRING() | SUBSTRING() |
| In-string | CHARINDEX() | INSTR() | POSITION() |
| Concatenate | + | CONCAT() | ` |
Let me know which SQL database you're using, and I can tailor a string function cheat sheet specifically for that system!
