SQL SUBSTRING()

In SQL, the SUBSTRING() function is used to extract substring from a string.

Example

-- extract the first 3 characters from the first names of each customer

SELECT customer_id, SUBSTRING(first_name, 1, 3) AS first_name_short
FROM Customers;

Here, we have used SUBSTRING() to extract the first three characters of each customer's first names.

The function takes three arguments: the string column, the start position, and the length of the substring.


SUBSTRING() With JOIN

In SQL, the SUBSTRING() function can also be used in more complex scenarios, such as in conjunction with JOIN operations.

Let's look at an example.

-- extract part of item names in order details

SELECT o.order_id, SUBSTRING(o.item, 1, 5) AS item_short, o.amount
FROM Orders o
JOIN Customers c ON o.customer_id = c.customer_id
WHERE c.country = 'UK';

In this SQL command, we're joining the Customers and Orders tables and extracting the first five characters of each item name in orders made by UK customers.

Visit SQL JOIN to learn more about the JOIN clause in SQL.


SUBSTRING() With WHERE Clause in SQL

We can use the SUBSTRING() function with the WHERE clause to filter data based on specific string patterns. For example,

-- select customers with 'Jo' at the beginning of their first name

SELECT customer_id, first_name, last_name
FROM Customers
WHERE SUBSTRING(first_name, 1, 2) = 'Jo';

Here, the SQL command filters the Customers table to include only those customers whose first name starts with Jo.

Did you find this article helpful?

Our premium learning platform, created with over a decade of experience and thousands of feedbacks.

Learn and improve your coding skills like never before.

Try Programiz PRO
  • Interactive Courses
  • Certificates
  • AI Help
  • 2000+ Challenges