SQL String Functions

SQL string functions are used to work with text data stored in database tables. They help perform operations such as changing letter cases, finding the length of a string, combining text values, and extracting part of a string. These functions make it easier to manage and retrieve text data according to specific requirements.

For example, an employee table may contain employee names, email addresses, and department names. SQL string functions can help convert names to uppercase, remove extra spaces, or combine first and last names into a single value.

Types of SQL String Functions

SQL provides several string functions to perform different operations on text values. The following table describes some commonly used SQL string functions.

Example of SQL String Functions

Consider the following Employees table:

We can use SQL string functions to format employee names, combine text, and retrieve specific parts of a string.

1. UPPER() Function

The UPPER() function converts all lowercase letters in a string to uppercase. It is useful when text needs to be displayed in capital letters, such as employee names, department names, or codes.

Syntax:

SELECT UPPER(column_name) FROM table_name;

Example:

SELECT UPPER(FirstName) AS UpperName FROM Employees;

Output:

Explanation:

In this example, UPPER(FirstName) converts each employee's first name to uppercase letters. For example, Amit becomes AMIT. The original values stored in the table remain unchanged because the query only changes how the results are displayed.

2. LOWER() Function

The LOWER() function converts uppercase letters in a string to lowercase. It is useful when text needs to follow a consistent lowercase format, such as displaying names or preparing text for comparisons.

Syntax:

SELECT LOWER(column_name) FROM table_name;

Example:

SELECT LOWER(FirstName) AS LowerName FROM Employees;

Output:

Explanation:

Here, LOWER(FirstName) converts each first name to lowercase letters. For example, Priya becomes priya. This function changes the output of the query without modifying the original data in the table.

3. LENGTH() Function

The LENGTH() function returns the length of a string. It helps determine how many characters or bytes a text value contains, depending on the database system and function used. It is useful for checking the length of names, usernames, and other text fields.

Syntax:

SELECT LENGTH(column_name) FROM table_name;

Example:

SELECT FirstName, LENGTH(FirstName) AS NameLength FROM Employees;

Output:

Explanation:

In this example, LENGTH(FirstName) calculates the length of each employee's first name. For example, Amit contains four characters, so the result is 4. The exact function name may differ between database systems; for example, SQL Server commonly uses LEN().

4. CONCAT() Function

The CONCAT() function combines two or more strings into a single string. It is commonly used to combine first and last names, join text values, or create complete labels from separate columns.

Syntax:

SELECT CONCAT(string1, string2) FROM table_name;

Example:

SELECT CONCAT(FirstName, ' ', LastName) AS FullName FROM Employees;

Output:

Explanation:

Here, CONCAT() combines the FirstName and LastName values. The ' ' adds a space between the two names, creating a complete name in the result. For example, Amit and Sharma become Amit Sharma.

5. SUBSTRING() Function

The SUBSTRING() function extracts a portion of a string from a specified starting position. It is useful when we need only part of a text value, such as a section of a code, an identifier, or a name.

Syntax:

SELECT SUBSTRING(string, start, length);

Example:

SELECT SUBSTRING(FirstName, 1, 3) AS ShortName FROM Employees;

Output:

Explanation:

In this example, SUBSTRING(FirstName, 1, 3) extracts three characters from each first name, starting at position 1. Therefore, Amit becomes Ami, and Priya becomes Pri. The exact syntax may vary slightly between database systems.

6. TRIM() Function

The TRIM() function removes spaces from the beginning and end of a string. It is useful when text contains unwanted spaces, which may cause problems when searching, comparing, or displaying data.

Syntax:

SELECT TRIM(column_name) FROM table_name;

Example:

SELECT TRIM('  Amit Sharma  ') AS CleanName;

Output:

Explanation:

Here, TRIM() removes the extra spaces before and after Amit Sharma. The text inside the string remains unchanged. This function is useful for cleaning text values that contain unnecessary spaces.

7. REPLACE() Function

The REPLACE() function replaces all occurrences of a specified text value with another value within a string. It is useful for correcting text, updating displayed values, or replacing particular words and characters in query results.

Syntax:

SELECT REPLACE(string, old_text, new_text);

Example:

SELECT REPLACE('SQL String Functions', 'String', 'Text') AS UpdatedText;

Output:

Explanation:

In this example, REPLACE() searches for the word String and replaces it with Text. The resulting value is SQL Text Functions. This query changes the returned result but does not update any stored table data.

8. LEFT() Function

The LEFT() function returns a specified number of characters from the beginning of a string. It is useful when we need to display the first few characters of a name, code, or other text value.

Syntax:

SELECT LEFT(string, number_of_characters);

Example:

SELECT LEFT(FirstName, 3) AS ShortName FROM Employees;

Output:

Explanation:

Here, LEFT(FirstName, 3) returns the first three characters of each employee's first name. For example, Rahul becomes Rah. This function makes it easy to retrieve the beginning of a string without extracting the entire value.

9. RIGHT() Function

The RIGHT() function returns a specified number of characters from the end of a string. It is useful for retrieving the last few characters of a code, identifier, or text value.

Syntax:

SELECT RIGHT(string, number_of_characters);

Example:

SELECT RIGHT(FirstName, 3) AS EndingCharacters FROM Employees;

Output:

Explanation:

In this example, RIGHT(FirstName, 3) returns the last three characters of each employee's first name. For example, Amit becomes mit, while Priya becomes iya. The function is useful when only the ending portion of a string is required.

Important Points About SQL String Functions

• SQL string functions are used to manipulate and retrieve text data.
• UPPER() and LOWER() change the letter case in query results.
• LENGTH() helps determine the length of a string, although its behavior can vary between database systems.
• CONCAT() combines multiple text values into one string.
• SUBSTRING(), LEFT(), and RIGHT() extract specific portions of a string.
• TRIM() removes leading and trailing spaces.
• REPLACE() substitutes specified text within a string.
• String functions can be used with SELECT statements and other SQL expressions to process text data.