Understanding String Functions in SQL | Beginner’s Complete Guide

Introduction

String functions in SQL allow you to manipulate, format, and modify text data within your database. Whether you're working with names, addresses, or descriptions, SQL provides a wide range of functions to help you manage string data efficiently. In this section, you will learn about the most common string functions in SQL.

1. CONCAT() – Concatenating Strings

The CONCAT() function allows you to combine two or more strings into one string.

Syntax:

CONCAT(string1, string2, ...);

Example:

SELECT CONCAT(first_name, ' ', last_name) AS full_name
FROM employees;

This combines the first name and last name of employees into a single full name.

2. LENGTH() – Finding the Length of a String

The LENGTH() function returns the number of characters in a string, helping you measure the size of text data.

Syntax:

LENGTH(string);

Example:

SELECT LENGTH(name) AS name_length
FROM employees;

This shows the number of characters in the name of each employee.

3. UPPER() and LOWER() – Changing Case of Text

The UPPER() function converts a string to uppercase, while LOWER() converts it to lowercase.

Syntax:

UPPER(string);
LOWER(string);

Example:

SELECT UPPER(first_name) AS uppercase_name
FROM employees;

This converts the first name of each employee to uppercase.

4. SUBSTRING() – Extracting a Substring from a String

The SUBSTRING() function allows you to extract a part of a string, starting at a specific position.

Syntax:

SUBSTRING(string, start_position, length);

Example:

SELECT SUBSTRING(first_name, 1, 3) AS short_name
FROM employees;

This extracts the first 3 characters from the first name of each employee.

5. TRIM() – Removing Leading and Trailing Spaces

The TRIM() function removes any leading or trailing spaces from a string, ensuring the text is clean.

Syntax:

TRIM(string);

Example:

SELECT TRIM(name) AS clean_name
FROM employees;

This removes any extra spaces from the name of employees.

6. REPLACE() – Replacing Substrings in a String

The REPLACE() function allows you to replace a specified substring with another substring within a string.

Syntax:

REPLACE(string, old_substring, new_substring);

Example:

SELECT REPLACE(address, 'Street', 'St.') AS shortened_address
FROM employees;

This replaces the word 'Street' with 'St.' in the address field.

7. INSTR() – Finding the Position of a Substring

The INSTR() function returns the position of the first occurrence of a substring within a string.

Syntax:

INSTR(string, substring);

Example:

SELECT INSTR(email, '@') AS at_position
FROM employees;

This shows the position of the '@' symbol in each employee's email address.

8. LEFT() and RIGHT() – Extracting Substrings from Left or Right

The LEFT() function extracts a specified number of characters from the beginning of a string.

The RIGHT() function extracts a specified number of characters from the end of a string.

Syntax:

LEFT(string, number_of_characters);
RIGHT(string, number_of_characters);

Example:

SELECT LEFT(first_name, 3) AS left_name
FROM employees;

SELECT RIGHT(last_name, 3) AS right_name
FROM employees;

The first query returns the first 3 characters of the first name.

The second query returns the last 3 characters of the last name.

9. CHARINDEX() (SQL Server) – Finding the Position of a Substring

For SQL Server, the CHARINDEX() function returns the position of a substring within a string, similar to INSTR() in other databases.

Syntax:

CHARINDEX(substring, string);

Example:

SELECT CHARINDEX('@', email) AS at_position
FROM employees;

This will return the position of '@' in the email address field.

10. CONCAT_WS() – Concatenating with a Separator

The CONCAT_WS() function is useful when you want to concatenate multiple strings with a specific separator.

Syntax:

CONCAT_WS(separator, string1, string2, ...);

Example:

SELECT CONCAT_WS('-', first_name, last_name) AS full_name
FROM employees;

This concatenates first name and last name with a hyphen in between.

Conclusion

String functions in SQL are essential tools for manipulating, cleaning, and formatting text data. By mastering these functions, you'll be able to handle various tasks such as:

Cleaning data by removing unwanted spaces.
Extracting specific portions of strings.
Transforming text into different formats.