SQL SUBSTRING()

The SQL SUBSTRING() function extracts a portion of a string, starting from a specified position for a specified length.

The SUBSTRING() function is useful for retrieving parts of text fields, such as splitting codes, extracting specific words, or manipulating substrings within data.

The SUBSTRING() function is supported by SQL Server, MySQL, PostgreSQL, and other major SQL databases.

In this tutorial, we will go through SQL SUBSTRING() String function, its syntax, and how to use this function in SQL statements for string operations, with the help of well detailed examples.

Syntax of SQL SUBSTRING() Function

The basic syntax of the SQL SUBSTRING() function is:

</>
Copy
SUBSTRING(string, start, length);

Each part of this syntax has a specific purpose:

  • string: The original string from which you want to extract a substring.
  • start: The starting position (1-based) for extracting the substring.
  • length: The number of characters to extract from the starting position.

The SUBSTRING() function returns the specified portion of the string based on the start and length values.

Examples: Using SUBSTRING() in SQL Queries

We’ll go through examples demonstrating the SUBSTRING() function in SQL. These examples show how to extract specific parts of strings without using any additional functions.

1. Extracting Specific Characters from an Email Address

To extract the first five characters from an email address:

</>
Copy
SELECT SUBSTRING('user@example.com', 1, 5) AS first_part;

This query returns “user@” as it extracts the first five characters of the email address.

SQL SUBSTRING() - Example: Extracting Specific Characters from an Email Address

2. Extracting a Substring from a Phone Number

To extract the middle section (characters 5 through 7) of a phone number:

</>
Copy
SELECT SUBSTRING('123-456-7890', 5, 3) AS middle_part;

This query returns “456”, which represents the middle section of the phone number.

SQL SUBSTRING() - Example: Extracting a Substring from a Phone Number

3. Extracting a Substring from a Date

If a date is stored in YYYY-MM-DD format, this query extracts the month:

</>
Copy
SELECT SUBSTRING('2024-09-15', 6, 2) AS month;

This query returns “09”, which represents the month part of the date.

SQL SUBSTRING() - Example: Extracting a Substring from a Date

4. Combining Parts of Two Strings

To combine the first two characters of a first name with the first two characters of a last name:

</>
Copy
SELECT CONCAT(SUBSTRING('Alice', 1, 2), SUBSTRING('Johnson', 1, 2)) AS short_name;

This query returns “AlJo”, by taking the first two characters from “Alice” and “Johnson”.

Reference: SQL CONCAT()

SQL SUBSTRING() - Example: Combining Parts of Two Strings

FAQs for SQL SUBSTRING()

1. What does the SQL SUBSTRING() function do?

The SUBSTRING() function extracts a portion of a string, starting at a specified position for a specified length.

2. Can SUBSTRING() handle NULL values?

If the input string is NULL, SUBSTRING() returns NULL.

3. Is SUBSTRING() supported in all SQL databases?

Yes, SUBSTRING() is supported by SQL Server, MySQL, PostgreSQL, and other SQL databases.

4. How does SUBSTRING() differ from LEFT() and RIGHT()?

SUBSTRING() allows you to specify any starting point within a string, while LEFT() and RIGHT() extract from the beginning or end.

5. Can SUBSTRING() be used with other functions?

Yes, SUBSTRING() can be combined with functions like CONCAT(), CHARINDEX(), and LEN() for more complex string manipulation.