Teradata SQL Position function with Example

Teradata SQL Position function returns the starting position of substring in a given string. If the substring is not present in the string then it returns 0. Teradata SQL Position is very useful when you need to extract some part of the string based on some specific pattern. Let us see an example for POSITION function:

CREATE MULTISET VOLATILE TABLE VT_ACTORS
(
S_NO INTEGER,
FULL_NAME VARCHAR(30)
)
PRIMARY INDEX(S_NO)
ON COMMIT PRESERVE ROWS;

INSERT INTO VT_ACTORS SEL 1, 'WOODY ALLEN' ;
INSERT INTO VT_ACTORS SEL 2, 'JAY LENO' ;
INSERT INTO VT_ACTORS SEL 3, 'JERRY SEINFELD' ;

Let’s find existence of string “EN” in name of ACTORS in the table. Below is the syntax for Teradata SQL Position:

SEL S_NO, FULL_NAME, POSITION('EN' IN FULL_NAME) AS POS
FROM VT_ACTORS;

Teradata SQL Position Example
Teradata SQL Position Example

Let’s use the POSITION function to select FIRST name and LAST name from FULL name in the same table.

SEL S_NO, POSITION(' ' IN FULL_NAME) AS POS, FULL_NAME, SUBSTR(FULL_NAME,1,POS) AS FIRST_NAME , SUBSTR(FULL_NAME,POS+1) AS LAST_NAME
FROM VT_ACTORS;

Teradata SQL Position Example
Teradata SQL Position Example

Another alternative for SQL POSITION is SQL INDEX. Both work similar for almost all character set. The syntax for TERADATA SQL INDEX is below:

SEL S_NO, INDEX(FULL_NAME,' ') AS POS, FULL_NAME, SUBSTR(FULL_NAME,1,POS) AS FIRST_NAME , SUBSTR(FULL_NAME,POS+1) AS LAST_NAME
FROM VT_ACTORS;

Leave a Reply

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