Teradata WITH Clause is an optional clause that always precedes SELECT clause in the query statements. Each subquery in the WITH clause specifies a table name, an optional list of column names, and a query expression that evaluates to a table (usually a SELECT statement). In SQL, WITH clause are commonly referred to as Common Table Expressions (CTE). Teradata WITH Clause WITH clause is used for many purposes, if you want to find our hierarchy in the data then recursive WITH clause is used. If your requirement is to reuse…

If you are working on the huge amount of different source system then you may come across the requirement of Teradata split delimited fields into table records. You can perform Teradata split delimited string into columns in various ways using Teradata built-in string functions or Teradata regular expressions. You can use any of the below methods as per your requirements: Teradata Split Delimited fields using STRTOK_SPLIT_TO_TABLE Function  Since TD14, there is a STRTOK_SPLIT_TO_TABLE function. You can use this function to split your string or delimited field into table columns. Teradata…

The Teradata regular expressions functions identify precise patterns of characters and are useful for extracting string from the data and validation of the existing data, for example, validate date, range checks, checks for characters, and extract specific characters from the data. In this article, we will check some of commonly used Teradata regular expressions. Read: Teradata String Functions and Examples Commonly used Teradata date functions and Examples Teradata Substring Regular Expression - REGEXP_SUBSTR This function is used to extracts a substring from source_string that matches a regular expression specified by…

Teradata String Functions are primarily used for various string manipulation. It also supports most of the standard string function along with the Teradata extension to those functions. Teradata String Functions Below are the commonly used Teradata string functions: Read: Teradata Regular Expressions and Examples Teradata Set Operators: UNION, UNION ALL, INTERSECT, EXCEPT/MINUS Commonly used Teradata Analytics Functions and Examples Teradata Date Functions and Examples Function Description concat(string1, ..., stringN) Returns the concatenation of two or more string values. This function provides the same functionality as the SQL-standard concatenation operator (||). length(string) Returns…

You can use the Teradata set operators to combine similar data sets from two or more SELECT statements in the query. The data types of the columns which are being used in the Teradata SET operators should match or explicitly type cast column values to required data types. The SET operators are similar to the JOINs, the only difference is that join combines the columns from different tables whereas SET operators combine rows from different tables. Read: Teradata String Functions and Examples Commonly used Teradata Date Functions and Examples Teradata…

This article is about detailed descriptions and examples of the commonly used Teradata date functions that you can use to manipulate date columns in the Teradata, stored procedure or in embedded SQLs. In the real word scenario, many application manipulate the date and time data types. Date types are highly formatted and very complicated. Each date value contains the century, year, month, day, hour, minute, and second. Each RDBMS may employ different date functions, and there may also be differences in the syntax for each RDBMS even when the function…

This article is about how Teradata data distribution works on the various AMP's present in the Teradata system. What is AMP? Before going in detail in the Teradata data distribution, lets check what AMP is? AMP, acronym for Access Module Processor, is the Virtual Processor (vproc) used to manage the database, handle file tasks and and manipulate the disk subsystem in the multi-tasking and possibly parallel-processing environment of the Teradata Database. Each AMP then contained its own microprocessor, disk drive, file system, database software (Database Manager), Teradata Operating System (TOS),…