DB2 Tutorial

  • Home
  • Start Here
  • Basics
  • Views
  • Triggers
  • Indexes
  • Functions
    • Aggregate Functions
    • Date Functions
    • String Functions
    • Window Functions
Home » Db2 String Functions » Db2 SUBSTRING

Db2 SUBSTRING

Summary: in this tutorial, you will learn how to use the Db2 SUBSTRING() function to extract a substring from a string.

Db2 SUBSTRING() function overview

The SUBSTRING() function allows you to extract a substring from a string. The basic syntax of the SUBSTRING() function is the following:

SUBSTRING( source_string, start_position [, substring_length ] );

The SUBSTRING() function returns a substring from the source_string starting at start_position with the substring_length length.

The following explains the meanings of the three arguments:

 source_string

The source_string is the source string from which you want to extract a substring.

 start_position

The start_position is a positive integer that specifies the location where the substring starts.

 substring_length

The substring_length specifies the number of characters of the substring to be extracted.

If you skip the substring_length, the function returns the rest of the source_string starting from the start_position location.

Db2 SUBSTRING() function examples

Let’s take some examples of using the SUBSTRING() function to understand it better.

1) Using Db2 SUBSTRING() function to extract a substring example

This example uses the SUBSTRING() function to extract a substring whose length is 3 starting from the first character of the source string:

SELECT SUBSTRING( 'Db2 Substring', 1, 3 ) Result FROM sysibm.sysdummy1;

Here is the output:

RESULT ------------- Db2

2) Using Db2 SUBSTRING() function with no substring’s length argument example

This example extracts a substring from the fifth position of the string 'Db2 Substring' to the rest of the string.

SELECT SUBSTRING( 'Db2 Substring', 5 ) Result FROM sysibm.sysdummy1;

The following shows the output:

RESULT ------------- Substring

3) Using Db2 SUBSTRING() function with table data example

Consider the following authors table in the sample database:

This example uses the SUBSTRING() to extract the initials of the author’s last names and the initial count for each initial.

SELECT SUBSTRING( first_name, 1, 1 ) initials , COUNT(*) initial_count FROM authors GROUP BY SUBSTRING( first_name, 1, 1 ) ORDER BY initials;

This picture shows the partial result set:

db2 substring function example

In this tutorial, you have learned how to use the Db2 SUBSTRING() function to extract a substring from a string.

  • Was this tutorial helpful ?
  • YesNo
Previous Db2 RTRIM
Next Db2 TRIM

Getting Started

  • What is Db2
  • Installing Db2 Database Server
  • Db2 Sample Database
  • Creating a Db2 Sample Database
  • Connecting to a Db2 Database
  • Interacting with Db2 using SQL Developer

Data Manipulation

  • SELECT
  • ORDER BY
  • WHERE
  • SELECT DISTINCT
  • AND
  • OR
  • BETWEEN
  • LIKE
  • IN
  • LIMIT
  • FETCH
  • Join
  • INNER JOIN
  • LEFT JOIN
  • RIGHT JOIN
  • FULL OUTER JOIN
  • Self-Join
  • CROSS JOIN
  • GROUP BY
  • Subquery
  • HAVING
  • UNION
  • INTERSECT
  • EXCEPT
  • Common Table Expression or CTE
  • INSERT
  • INSERT Multiple Rows
  • INSERT INTO SELECT
  • UPDATE
  • DELETE

Managing Database Objects

  • CREATE TABLE
  • Identity Columns
  • ALTER COLUMN
  • ADD COLUMN
  • DROP COLUMN
  • DROP TABLE
  • TRUNCATE TABLE
  • RENAME TABLE

Db2 Constraints

  • Primary Key
  • Foreign Key
  • DEFAULT
  • NOT NULL
  • CHECK
  • UNIQUE

Db2 Data Types

  • Integer
  • Decimal
  • VARCHAR
  • CHAR
  • DATE
  • TIME
  • TIMESTAMP

Useful Functions & Expressions

  • CAST
  • CASE Expression
  • COALESCE

About db2tutorial.com

The db2tutorial.com website provides you with a comprehensive IBM DB2 tutorial with many practical examples and hands-on sessions.

Recent Tutorials

  • Db2 Functions
  • Db2 RANK
  • Db2 ROW_NUMBER
  • Db2 Window Functions
  • What is Db2

Site Links

  • Home
  • About Us
  • Contact Us
  • Privacy Policy
Copyright © © 2021 by www.db2tutorial.com. All Rights Reserved.