The Project | About Us | Contribute | Donations | License

HOME

String functions in SQL are used to manipulate and handle string data. These functions are essential for tasks such as formatting, searching, and modifying text data. In this section, we will cover some of the most commonly used string functions in SQL.

Key Concepts

Concatenation: Combining two or more strings into one.
Substring Extraction: Extracting a part of a string.
String Length: Determining the length of a string.
String Replacement: Replacing part of a string with another string.
String Trimming: Removing unwanted characters from the beginning or end of a string.
String Case Conversion: Changing the case of characters in a string.

Common String Functions

CONCAT

The CONCAT function is used to concatenate two or more strings.

Syntax:

CONCAT(string1, string2, ...)

Example:

SELECT CONCAT('Hello', ' ', 'World') AS Greeting;

Output:

Greeting
---------
Hello World

SUBSTRING

The SUBSTRING function extracts a substring from a string.

Syntax:

SUBSTRING(string, start_position, length)

Example:

SELECT SUBSTRING('Hello World', 7, 5) AS Substring;

Output:

Substring
---------
World

LENGTH

The LENGTH function returns the number of characters in a string.

Syntax:

LENGTH(string)

Example:

SELECT LENGTH('Hello World') AS StringLength;

Output:

StringLength
------------
11

REPLACE

The REPLACE function replaces occurrences of a specified substring within a string.

Syntax:

REPLACE(string, old_substring, new_substring)

Example:

SELECT REPLACE('Hello World', 'World', 'SQL') AS ReplacedString;

Output:

ReplacedString
--------------
Hello SQL

TRIM

The TRIM function removes leading and trailing spaces from a string.

Syntax:

TRIM(string)

Example:

SELECT TRIM('   Hello World   ') AS TrimmedString;

Output:

TrimmedString
-------------
Hello World

UPPER and LOWER

The UPPER function converts all characters in a string to uppercase, while the LOWER function converts all characters to lowercase.

Syntax:

UPPER(string)
LOWER(string)

Example:

SELECT UPPER('Hello World') AS UppercaseString, LOWER('Hello World') AS LowercaseString;

Output:

UppercaseString | LowercaseString
----------------|----------------
HELLO WORLD     | hello world

Practical Exercises

Exercise 1: Concatenation

Write a query to concatenate the first name and last name of employees in the employees table.

Solution:

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

Exercise 2: Substring Extraction

Write a query to extract the domain from an email address in the users table.

Solution:

SELECT SUBSTRING(email, INSTR(email, '@') + 1) AS Domain FROM users;

Exercise 3: String Length

Write a query to find the length of the product names in the products table.

Solution:

SELECT product_name, LENGTH(product_name) AS NameLength FROM products;

Exercise 4: String Replacement

Write a query to replace all occurrences of 'old' with 'new' in the descriptions column of the items table.

Solution:

SELECT REPLACE(description, 'old', 'new') AS UpdatedDescription FROM items;

Exercise 5: String Trimming

Write a query to trim leading and trailing spaces from the comments column in the feedback table.

Solution:

SELECT TRIM(comments) AS TrimmedComments FROM feedback;

Exercise 6: Case Conversion

Write a query to convert the city column in the addresses table to uppercase.

Solution:

SELECT UPPER(city) AS UppercaseCity FROM addresses;

Common Mistakes and Tips

Incorrect Syntax: Ensure you use the correct syntax for each function.
Case Sensitivity: Remember that string functions can be case-sensitive depending on the SQL dialect.
Null Values: Be cautious when handling null values, as they can affect the output of string functions.

Conclusion

In this section, we covered essential string functions in SQL, including concatenation, substring extraction, string length, replacement, trimming, and case conversion. These functions are powerful tools for manipulating and handling text data in your SQL queries. Practice using these functions with the provided exercises to reinforce your understanding. In the next section, we will explore numeric functions in SQL.

String Functions

Key Concepts

Common String Functions

CONCAT

SUBSTRING

LENGTH

REPLACE

TRIM

UPPER and LOWER

Practical Exercises

Exercise 1: Concatenation

Exercise 2: Substring Extraction

Exercise 3: String Length

Exercise 4: String Replacement

Exercise 5: String Trimming

Exercise 6: Case Conversion

Common Mistakes and Tips

Conclusion

SQL Course

Module 1: Introduction to SQL

Module 2: Basic SQL Queries

Module 3: Working with Multiple Tables

Module 4: Advanced Data Filtering

Module 5: Data Manipulation

Module 6: Advanced SQL Functions

Module 7: Subqueries and Nested Queries

Module 8: Indexes and Performance Optimization

Module 9: Transactions and Concurrency

Module 10: Advanced Topics

Module 11: SQL in Practice

Module 12: Final Project