fbpx

The Teradata SUBSTRING Function

By DWH Pro Admin

November 18, 2019


Teradata SUBSTR Or SUBSTRING?

The Teradata SUBSTRING or SUBSTR function is one of the Teradata string functions and used to cut a substring from a string based on its position.

We can use ANSI syntax (Teradata SUBSTRING) or Teradata syntax (Teradata SUBSTR). The ANSI syntax is recommended to remain compatible with other database systems.

ANSI Syntax:

SUBSTRING(str FROM pos [FOR count]);
Parameters:
Mandatory:
str is the string expression from which the substring is taken.
pos is the starting position in str
Optional:
[FOR] 
count is the length of the substring (optional). FOR count is omitted, the substring starts at position pos and takes all remaining characters from the string to the end.

Teradata Syntax:

SUBSTR(str,pos[,count]);
Parameters: 
Mandatory:
str is the string expression from which the substring is taken.
pos is the starting position in str
Optional:
[,count] 
count is the length of the substring (optional). FOR count is omitted, the substring starts at position pos and takes all remaining characters from the string to the end.
The Teradata SUBSTRING Function 1

Allowed Argument Types

  • Character
  • Byte
  • Numeric
  • User-Defined Type (UDT):
    are implicitly cast to one of the following types: CHARACTER, NUMERIC, DATE, BYTE

Data Type of Return Value

  • If str is BLOB then BLOB
  • Any Byte String (except BLOB) returns VARBYTE(n)
  • CLOB returns CLOB(n)
  • Character String or Numeric Value except for CLOB returns VARCHAR(n)

In ANSI mode, the value of n for each DATATYPE(n) is equal to the original string’s value.

In Teradata mode, the value of n depends on the number of characters/bytes in the result string.

Examples for Teradata SUBSTRING

Here is an example for the SUBSTRING function applied to a character string column:

SELECT * FROM Country WHERE SUBSTRING (country_desc FROM 1 FOR 3) = 'TUN';

Here is an example for the Teradata SUBSTRING function applied to a CLOB column:

SELECT SUBSTRING (myCLOB FROM 100 FOR 120000) FROM TheCLOBTable; 

Tuning with Teradata SUBSTRING

Both SUBSTRING and SUBSTR store only the requested characters/bytes in the spool, and not the entire column. Therefore it may make sense to use this function early in the ETL process (e.g., in a volatile table) if only a part of a column is needed.

__CONFIG_colors_palette__{"active_palette":0,"config":{"colors":{"62516":{"name":"Main Accent","parent":-1}},"gradients":[]},"palettes":[{"name":"Default Palette","value":{"colors":{"62516":{"val":"var(--tcb-skin-color-0)"}},"gradients":[]},"original":{"colors":{"62516":{"val":"rgb(19, 114, 211)","hsl":{"h":210,"s":0.83,"l":0.45}}},"gradients":[]}}]}__CONFIG_colors_palette__
__CONFIG_colors_palette__{"active_palette":0,"config":{"colors":{"b4fbe":{"name":"Main Accent","parent":-1}},"gradients":[]},"palettes":[{"name":"Default Palette","value":{"colors":{"b4fbe":{"val":"rgb(241, 99, 52)"}},"gradients":[]},"original":{"colors":{"b4fbe":{"val":"rgb(19, 114, 211)","hsl":{"h":210,"s":0.83,"l":0.45}}},"gradients":[]}}]}__CONFIG_colors_palette__
Previous Article
__CONFIG_colors_palette__{"active_palette":0,"config":{"colors":{"b4fbe":{"name":"Main Accent","parent":-1}},"gradients":[]},"palettes":[{"name":"Default Palette","value":{"colors":{"b4fbe":{"val":"rgb(241, 99, 52)"}},"gradients":[]},"original":{"colors":{"b4fbe":{"val":"rgb(19, 114, 211)","hsl":{"h":210,"s":0.83,"l":0.45}}},"gradients":[]}}]}__CONFIG_colors_palette__
Next Article
Buy the Book Teradata Query Performance Tuning

DWH Pro Admin

Roland Wenzlofsky is an experienced freelance Teradata Consultant & Performance Trainer. Born in Austria's capital Vienna, he is building and tuning some of the largest Teradata Data Warehouses in the European financial and telecommunication sectors for more than 20 years. He has played all the roles of developer, designer, business analyst, and project manager. Therefore, he knows like no other the problems and obstacles that make many data warehouse projects fail and all the tricks and tips that will help you succeed.

{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}

You might also like

>