Essential SQL String Functions Every Data Professional Should Know

Introduction

SQL provides a variety of string functions that are essential for working with text data in databases. In this guide, we’ll cover some common SQL string functions with examples, using students and branch tables. Each function includes a query and sample output so you can see the results in action.

Topics Covered

UPPER and LOWER Functions
LENGTH Function
TRIM Function
Boolean Expressions
Concatenation
Substring Function
Position Function
COALESCE Function
REPLACE Function

Table Creation and Data Insertion

To follow along with these examples, start by creating and populating the students and branch tables.

1. Creating the `branch` Table

CREATE TABLE branch (
    branch_id INT PRIMARY KEY,
    branch_name VARCHAR(50),
    department VARCHAR(50)
);

-- Inserting sample data into branch table
INSERT INTO branch (branch_id, branch_name, department) VALUES
(1, 'Computer Science', 'Engineering'),
(2, 'Electronics', 'Engineering'),
(3, 'Mechanical', 'Engineering'),
(4, 'Civil', 'Engineering'),
(5, 'Biotechnology', 'Science'),
(6, 'Physics', 'Science'),
(7, 'Mathematics', 'Science'),
(8, 'Chemistry', 'Science'),
(9, 'Commerce', 'Commerce'),
(10, 'Accounting', 'Commerce');

Example Output:

2. Creating the `students` Table

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email VARCHAR(100),
    score INT,
    branch_id INT,
    FOREIGN KEY (branch_id) REFERENCES branch(branch_id)
);

-- Inserting sample data into students table
INSERT INTO students (student_id, first_name, last_name, email, score, branch_id) VALUES
(1, 'Aarav', 'Sharma', NULL, 85, 1),
(2, 'Vihaan', 'Verma', 'vihaan.verma@manyamdatalabs.com', 92, 2),
(3, 'Aditya', 'Singh', NULL, 76, 3),
(4, 'Ananya', 'Fernandes', 'ananya.Fernandes@manyamdatalabs.com', 88, 4),
(5, 'Ishaan', 'Reddy', 'ishaan.reddy@manyamdatalabs.com', 95, 5),
(6, 'Sara', 'Nair', 'sara.nair@manyamdatalabs.com', 81, 6),
(7, 'Aryan', 'Patel', 'aryan.patel@manyamdatalabs.com', 73, 7),
(8, 'Esha Krishna', 'Manyam', 'esha.manyam@manyamdatalabs.com', 89, 8),
(9, 'Saanvi', 'Joshi', 'saanvi.joshi@manyamdatalabs.com', 79, 9),
(10, 'Zakir', 'Hussain', 'zakir.hussain@manyamdatalabs.com', 84, 10);

Example Output

Now that we have our tables and sample data, let’s dive into each function.

Function Explanations with Examples and Sample Outputs

1. UPPER and LOWER Functions

Significance:
UPPER converts text to uppercase, and LOWER converts it to lowercase, which is helpful for standardizing data formats.

Example Query:

SELECT UPPER(branch_name) AS uppercase_branch, LOWER(department) AS lowercase_department FROM branch;

Sample Output:

2. LENGTH Function

Significance:
The LENGTH function returns the number of characters in a string, helpful for filtering data by text length.

Example Query:

SELECT branch_name, LENGTH(branch_name) AS branch_length FROM branch;

Sample Output:

3. TRIM Function

Significance:
TRIM removes leading and trailing spaces from a string, ideal for cleaning up data.

Example Query:

SELECT TRIM('   Hello There!        ') AS trimmed_text;

Sample Output:

4. Boolean Expressions

Significance:
Boolean expressions evaluate conditions, returning TRUE or FALSE. Useful for logical checks.

Example Query:

SELECT first_name, score, (score > 75) AS is_passing FROM students;

Sample Output:

5. Concatenation

Significance:
Concatenation combines multiple strings, often used to join names or add labels to data.

Example Query:

SELECT first_name, last_name, first_name || ' ' || last_name AS full_name FROM students;

Sample Output:

6. Substring Function

Significance:
The SUBSTRING function extracts part of a string, useful for retrieving specific text portions like initials or domains.

Example Query:

SELECT SUBSTRING('Hello Esha Krishna Manyam' FROM 7) AS substring_text;

Sample Output:

7. Position Function

Significance:
The POSITION function finds the index of a character or substring within a text field.

Example Query:

SELECT POSITION('@' IN email) AS at_position FROM students;

Sample Output:

8. COALESCE Function

Significance:
COALESCE returns the first non-null value, useful for handling NULLs by providing a default.

Example Query:

SELECT first_name, COALESCE(email, 'N/A') AS email FROM students;

Sample Output:

9. REPLACE Function

Significance:
The REPLACE function substitutes parts of a string with new text, useful for reformatting or renaming.

Example Query:

SELECT REPLACE(branch_name, 'Electronics', 'ECE') AS updated_branch_name FROM branch;

Sample Output:

Exercises to Practice

Use these exercises to test your knowledge of SQL string functions:

Convert all last_name values in the students table to uppercase.
Find the length of each first_name in the students table.
Remove leading and trailing spaces from the text “ Welcome to SQL ”.
Concatenate first_name and last_name with a comma in between.
Check if each score in the students table is greater than or equal to 80 and display the result as is_top_scorer.
Extract the domain part of each email in the students table.
Replace all occurrences of “Science” with “Sci” in the branch table’s branch_name.
Create a query that combines first_name, last_name, and branch_name of each student with proper spacing.
Write a query that finds the position of the character ‘a’ in each first_name in the students table.
Display the branch_name and department, with department in lowercase.
Show the trimmed version of each branch_name in the branch table.
Find the length of each email in the students table.
Use COALESCE to display N/A for any NULL email entries in the students table.
Extract the first 5 characters from branch_name in the branch table.
Concatenate department and the word “Department” for each entry in the branch table.

Conclusion

Mastering SQL string functions allows you to manipulate, clean, and format text data effectively. Practice with these examples and exercises to build your skills in handling text-based fields, ensuring your data is clean, consistent, and ready for analysis.