How to select from a wildcard list and return the portion that matches?

Database
Highlighted

How to select from a wildcard list and return the portion that matches?

I have a CASE statement that uses a wildcard list to search a string (length may vary) for any of three values.  For example, the string might look like this:

 

1GC 1GD 1GN 1GV 2DA 2FA 2FG 3GC 3GV 3TA 3TP

 

The CASE statement is something like:

 

CASE WHEN mycolumn LIKE ANY ('%1Z%','%2D%','%3Z%') THEN 'Widget' ELSE 'Gadget' END as Product

 

Is it possible to return the portion of the wildcard that matches the condition?  Using the above examples, the value that matches the condition would be 2DA.  How can I select and display that matched condition value? 

 

Thank you for any help!


Accepted Solutions
Teradata Employee

Re: How to select from a wildcard list and return the portion that matches?

Hi lottidah,

 

You can go for REGEXP_SUBSTR with the | acting as an OR operator.

Some datas :

create multiset volatile table mvt_data, no log
( pi_id     byteint not null
, str_id    byteint not null
, str       varchar(50) not null
)
primary index (pi_id)
on commit preserve rows
;

insert into mvt_data values (1, 1, '1GC 1GD 1GN 1GV 2DA 2FA 2FG 3GC 3GV 3TA 3TP');
insert into mvt_data values (1, 2, '1GC 1GD 1GN 1GV 2DezezaA 2FA 2FG 3GC 3GV 3TA 3TP');
insert into mvt_data values (1, 3, '1GC 1GD 1GN 1GV 2DA      2FA 2FG 3GC 3GV 3TA 3TP');
insert into mvt_data values (1, 4, '1GC 1GD 1GN 1GV 2FA 2FG 3GC 3GV 3TA 3TP');

The query :

select str_id
     , regexp_substr(str, '(1Z|2D|3Z)\S*')
     , str
  from mvt_data
 where pi_id = 1
   and str like any ('%1Z%','%2D%','%3Z%');

It seems ok with those few lines.

 

1 ACCEPTED SOLUTION
3 REPLIES
Teradata Employee

Re: How to select from a wildcard list and return the portion that matches?

Hi lottidah,

 

You can go for REGEXP_SUBSTR with the | acting as an OR operator.

Some datas :

create multiset volatile table mvt_data, no log
( pi_id     byteint not null
, str_id    byteint not null
, str       varchar(50) not null
)
primary index (pi_id)
on commit preserve rows
;

insert into mvt_data values (1, 1, '1GC 1GD 1GN 1GV 2DA 2FA 2FG 3GC 3GV 3TA 3TP');
insert into mvt_data values (1, 2, '1GC 1GD 1GN 1GV 2DezezaA 2FA 2FG 3GC 3GV 3TA 3TP');
insert into mvt_data values (1, 3, '1GC 1GD 1GN 1GV 2DA      2FA 2FG 3GC 3GV 3TA 3TP');
insert into mvt_data values (1, 4, '1GC 1GD 1GN 1GV 2FA 2FG 3GC 3GV 3TA 3TP');

The query :

select str_id
     , regexp_substr(str, '(1Z|2D|3Z)\S*')
     , str
  from mvt_data
 where pi_id = 1
   and str like any ('%1Z%','%2D%','%3Z%');

It seems ok with those few lines.

 

Teradata Employee

Re: How to select from a wildcard list and return the portion that matches?

Hi

 

Regular expressions:

 

BTEQ -- Enter your SQL request or BTEQ command:
SELECT
TD_SYSFNLIB.REGEXP_SUBSTR(TheCol, '(?<=^| )(1Z|2D|3Z).(?= |$)', 1, 1, 'i')
FROM ( SELECT '1GC 1GD 1GN 1GV 2DA 2FA 2FG 3GC 3GV 3TA 3TP' TheCol ) pre
;


*** Query completed. One row found. One column returned.
*** Total elapsed time was 1 second.

REGEXP_SUBSTR(TheCol,'(?<=^| )(1Z|2D|3Z).(?= |$)',1,1,'i')
----------------------------------------------------------
2DA

 

HTH.

 

Cheers.

 

Carlos.

Re: How to select from a wildcard list and return the portion that matches?

Waldar, this solution worked perfectly in my brief testing.  Thank you so much for your help!