SQL SUBSTRING()

In SQL, the SUBSTRING() function takes a slice from a string containing data in binary, character, text, or image format. Upon extraction, the SELECT statement can be used to select and manipulate the extracted substring according to the data type. The data type is the same as the original string except for the next expressions.

SQL SUBSTRING() Syntax

SUBSTRING(string, start, length)

Parameters:

• string: The string to extract a substring from.
• start: The string index from which the substring starts. The minimum possible value is 1.
• length: The number of characters to extract from string.

Return value:

Returns a substring of the original string. If length exceeds the remaining characters, the result contains everything from start to the end of the string.

Note: Not all services support this function. Oracle and SQLite use SUBSTR() to accomplish the same goal.

Example 1: Using SUBSTRING() on a String Literal

This query uses SUBSTRING() on a string literal:

SELECT SUBSTRING('Codecademy', 1, 4) AS ExtractString;

Copy to clipboard
Copy to clipboard

Here is the output:

Example 2: Using SUBSTRING() on a Table Column

Suppose we have a table named Employees:

This query uses SUBSTRING() on the FullName column of the Employees table:

SELECT FullName, SUBSTRING(FullName, 1, 4) AS FirstFourChars
FROM Employees;

Copy to clipboard
Copy to clipboard

Here is the output:

Example 3: Extracting Domain from an Email Using SUBSTRING()

Suppose we have a table Users:

This query uses SUBSTRING() to extract the domains from the emails in the Email column of the Users table:

SELECT Email, SUBSTRING(Email, CHARINDEX('@', Email) + 1, LEN(Email)) AS Domain
FROM Users;

Copy to clipboard
Copy to clipboard

Here is the output:

Frequently Asked Questions

1. What is SUBSTRING() in SQL?

SUBSTRING() returns part of a string, starting at a specified character position, for a given length.

2. How do SUBSTRING() and SUBSTR() differ?

• SUBSTRING(): Standard SQL function, supported by many databases.
• SUBSTR(): Common in Oracle and some versions of MySQL, with similar functionality but slightly different parameter handling.

3. How to extract a substring from a string in SQL?

In MySQL, you can use either SUBSTRING() or SUBSTR(), they work the same way:

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

Copy to clipboard
Copy to clipboard

Or,

SELECT SUBSTR('Hello World', 7, 5) AS Result;

Copy to clipboard
Copy to clipboard

Here is the output: