SQL - String Functions

SQL String Functions

SQL provides a rich set of built-in functions to manipulate string data. These are essential for cleaning, formatting, and analyzing text-based information. While function names can vary slightly between different SQL dialects (e.g., PostgreSQL, MySQL, SQL Server), the core concepts are universal. We will focus on the most common, standard functions.

Concatenation: Combining Strings

Concatenation is the process of joining two or more strings together.

• CONCAT(string1, string2, ...): Joins the strings. If any argument is NULL, the result is NULL in some databases (like SQL Server) or the NULL is ignored in others (like MySQL/PostgreSQL).
• CONCAT_WS(separator, string1, string2, ...): Concatenate With Separator. This is very useful as it puts a separator between elements and intelligently skips NULL values.

SELECT CONCAT('John', ' ', 'Doe') AS full_name;
-- Result: 'John Doe'

SELECT CONCAT_WS(', ', 'New York', 'NY', NULL, 'USA') AS address;
-- Result: 'New York, NY, USA'

Changing Case: UPPER and LOWER

These functions are used to convert strings to all uppercase or all lowercase, which is especially useful for case-insensitive comparisons.

SELECT UPPER('Hello World') AS upper_case, LOWER('Hello World') AS lower_case;
-- Result: 'HELLO WORLD', 'hello world'

-- Case-insensitive search
SELECT * FROM users WHERE LOWER(username) = 'admin';

Length and Trimming: LENGTH, TRIM, LTRIM, RTRIM

These functions help you manage whitespace and find the length of a string.

• LENGTH(string) or LEN(string): Returns the number of characters in a string. (Name varies by database).
• TRIM(string): Removes leading and trailing whitespace.
• LTRIM(string): Removes leading (left) whitespace.
• RTRIM(string): Removes trailing (right) whitespace.

SELECT LENGTH('  sql is fun  ') AS with_spaces;
-- Result: 14

SELECT LENGTH(TRIM('  sql is fun  ')) AS trimmed;
-- Result: 11

Substrings: SUBSTRING, LEFT, RIGHT

These functions allow you to extract a part of a string.

• SUBSTRING(string FROM start FOR length): The standard syntax to extract a substring. Many databases also support SUBSTRING(string, start, length).
• LEFT(string, number_of_chars): Extracts a number of characters from the left side of a string.
• RIGHT(string, number_of_chars): Extracts a number of characters from the right side of a string.

SELECT SUBSTRING('abcdef' FROM 3 FOR 2) AS part;
-- Result: 'cd'

SELECT LEFT('report-2023.pdf', 6) AS type, RIGHT('report-2023.pdf', 3) AS extension;
-- Result: 'report', 'pdf'

Searching and Replacing: POSITION, REPLACE

These functions are used to find or change parts of a string.

• POSITION(substring IN string): Finds the starting position of a substring. Returns 0 if not found. (Other names: CHARINDEX, INSTR).
• REPLACE(string, from_substring, to_substring): Replaces all occurrences of a substring with another.

SELECT POSITION('world' IN 'hello world') AS found_at;
-- Result: 7

SELECT REPLACE('Your total is $100', '$', 'USD') AS updated_price;
-- Result: 'Your total is USD 100'

Important Considerations

Performance

Using functions on a column in a WHERE clause can prevent the database from using an index on that column, potentially leading to a full table scan and very slow queries. For example, WHERE LOWER(username) = 'admin' is generally not indexable. Some modern databases support ‘function-based indexes’ to solve this, but it requires explicit setup.

Security (SQL Injection)

CRITICAL: String functions should NEVER be used to construct SQL queries with user-provided data. This is a direct path to SQL Injection vulnerabilities. Always use parameterized queries (prepared statements) to safely insert user data into queries.

-- DANGEROUS - DO NOT DO THIS!
-- String concatenation to build a query.
query = "SELECT * FROM users WHERE username = '" + userInput + "';";

-- SAFE - USE THIS METHOD
-- Parameterized query (syntax varies by language/library)
query = "SELECT * FROM users WHERE username = ?;";
execute(query, [userInput]);