I have a coulmn (col1) in my table (Tab1) , where the values are like below .
The length are non-unique
and so even the values , separated by field separators.
The field separator is dash always.
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)
FROM TABLE(STRTOK_SPLIT_TO_TABLE(cte.keycol, cte.col1, '-')
RETURNS (outkey INTEGER,
token VARCHAR(100) CHARACTER SET UNICODE)
) AS dt
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