Value parsing in Teradata

General

Value parsing in Teradata

Hello , 

I have a coulmn (col1) in my table (Tab1) , where the values are like below .

Input

AA17AX-BBB123-CC123-YYY23233,

ABABA11-ABB2-CCCC222-XYXYX123-MNOP12 

The length are non-unique

and so even the values , separated by field separators.

The field separator is dash always.

This coulmn is used in my join condition .

I want to parse this field to have the individual values so that it can compare with by joining tables.

Values expected are  (In case of 1st eg)

AA17AX

BBB123

CC123

YYY23233

Can you please guide me on the same ?

2 REPLIES
N/A

Re: Value parsing in Teradata

Delimited data is absolutely worst case in a relational dabatase system.

The best solution would be fixing the data model. 

Otherwise it depends on your Teradata release.

Since TD14 there's the STRTOK_SPLIT_TO_TABLE function:

WITH cte (keycol, col1) AS
(SEL 1 AS keycol, 'ABABA11-ABB2-CCCC222-XYXYX123-MNOP12' AS col1)
SELECT *
FROM TABLE(STRTOK_SPLIT_TO_TABLE(cte.keycol, cte.col1, '-')
RETURNS (outkey INTEGER,
tokennum INTEGER,
token VARCHAR(100) CHARACTER SET UNICODE)
) AS dt

Re: Value parsing in Teradata

SELECT 1 WHERE 'AA17AX-BBB123-CC123-YYY23233' LIKE '%AA17AX-%' OR  'AA17AX-BBB123-CC123-YYY23233' LIKE '-AA17AX%'

May be you can simply make the join using LIKE