Home / Teradata / Teradata substring

Teradata substring

Substring function is used to trim a string from particular position as mentioned in the parameters.  Both SUBSTRING and SUBSTR solves the same purpose but have a different syntax. Here is the full elaborated use with syntax and examples.

What is TeraData?

Teradata is a fully scalable relational database management system produced by Teradata Corp. It is widely used to manage large data warehousing operations.

  • SUBSTR is a Teradata extension to the ANSI SQL:2008 standard.
  • SUBSTRING is ANSI SQL:2008 compliant.

SUBSTR – Use, Syntax & Example

How to use SUBSTR for cutting/trimming/extracting a text out of a string with syntax, example and output.

Syntax:

SUBSTR ( <column-name>, <start-position> [ , <length> ] )

Example:

SELECT
SUBSTR('Programcodelib.com', 8 , 4),
SUBSTR('Programcodelib.com', 1 , 7),
SUBSTR('Programcodelib.com', 12 , 3),
SUBSTR('Programcodelib.com', 1 , 3);

Output:

CommandOutputs
SUBSTR(‘Programcodelib.com’, 8 , 4),code
SUBSTR(‘Programcodelib.com’, 1 , 7),Program
SUBSTR(‘Programcodelib.com’, 12 , 3),lib
SUBSTR(‘Programcodelib.com’, 1 , 3);Pro

SUBSTRING – Use, Syntax & Example

How to use SUBSTR for cutting/trimming/extracting a text out of a string with syntax, example and output

Syntax

SUBSTRING ( <your input>, <start-position> [ , <length> ] ) 
or 
SUBSTRING ( <your input> FROM <start-position> FOR <length>)

Example:

SELECT
SUBSTRING('Programcodelib.com', 8 , 4),
SUBSTRING('Programcodelib.com', 1 , 7),
SUBSTRING('Programcodelib.com', 12 , 3),
SUBSTRING('Programcodelib.com', 1 , 3);

Or

SELECT
SUBSTRING('Programcodelib.com' FROM 8 FOR 4),
SUBSTRING('Programcodelib.com' FROM 1 FOR 7),
SUBSTRING('Programcodelib.com' FROM 12 FOR  3),
SUBSTRING('Programcodelib.com' FROM 1 FOR 3);

Output:

CommandOutputs
SUBSTRING(‘Programcodelib.com’, 8 , 4),code
SUBSTRING(‘Programcodelib.com’, 1 , 7),Program
SUBSTRING(‘Programcodelib.com’, 12 , 3),lib
SUBSTRING(‘Programcodelib.com’, 1 , 3);Pro
SUBSTRING(‘Programcodelib.com’ FROM 8 FOR 4),code
SUBSTRING(‘Programcodelib.com’ FROM 1 FOR 7),Program
SUBSTRING(‘Programcodelib.com’ FROM 12 FOR  3),lib
SUBSTRING(‘Programcodelib.com’ FROM 1 FOR 3);Pro

Leave a Reply

Your email address will not be published. Required fields are marked *