All Forums General
Jaistha 1 post Joined 04/15
09 Apr 2015
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 ?

 

 

 

dnoeth 4628 posts Joined 11/04
11 Apr 2015

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

 

Dieter

sravan4 8 posts Joined 02/15
12 Apr 2015
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
 

You must sign in to leave a comment.