What is the closet function to LIKE ANY in Teradata for Vertica?

Ravindra_Gudime

What is the closet function to LIKE ANY in Teradata for Vertica? Example query in Teradata SELECT id, cd LIKE ANY ('%M2MCNG%','%MOBMOB%') From abc

[Deleted User]

Hello Ravindra, thanks for your question! We'll see how other customers have transitioned from Teradata and their functions.

Sajan_Sangraula

Hi Rabindra, is this what you are looking for: select id, case when cd like '%M%' then cd else '' end from abc; If you actually want to filter out the rows, you can put the case in the where clause. hope that helps

ablack3

I would also really like to know how to create replicate this query:

SELECT id, cd LIKE ANY ('%M2MCNG%','%MOBMOB%') From abc

I don't think Sajan_Sangraula's suggestion will work for me.

Jim_Knicely

You can write it like this:

select id, cd from abc where cd like '%M2MCNG%' or cd like '%MOBMOB%';

Or like this:

select id, cd from abc where exists (select null from (select '%M2MCNG%' c union all select '%MOBMOB%' c) foo where cd ilike c);

Or like this:

with q as (select '%M2MCNG%' c union all select '%MOBMOB%' c) select id, cd from abc where exists (select null from q where cd ilike c);

Jim_Knicely

Actually, since you are using LIKE ANY as a column, this could work:

dbadmin=> select * from abc;
 id |      cd
----+--------------
  1 | M2MCNG
  2 | MOBMOB
  3 | NOT THIS ONE
(3 rows)

dbadmin=> select id, cd, (case when cd ilike '%M2MCNG%' then true when cd ilike '%MOBMOB%' then true else false end) like_any from abc;
 id |      cd      | like_any
----+--------------+----------
  1 | M2MCNG       | t
  2 | MOBMOB       | t
  3 | NOT THIS ONE | f
(3 rows)

ablack3

Thanks for the suggestions Jim. Your suggestions work great for a small number of match strings (2 in my example) but would be difficult to write as the number of possible match strings gets larger. Suppose I have a large number of different possible strings I'd like to try and match. Is there any way to write something like

SELECT dx_code LIKE ANY ('M05%', 'M06%', 'M315', 'M32%', 'M33%', 'M34%', 'M351', 'M353', 'M360') From dx_code_table

I can pretty easily generate SQL code like this for a large number of match strings. I suppose it would be possible to programmatically generate SQL using your first suggestion but it would be a little harder.

SELECT dx_code from dx_code_table
where dx_code like 'M05%'
or dx_code like 'M06%'
or dx_code like 'M315'
or dx_code like 'M32%'
or dx_code like 'M33%'
or dx_code like 'M34%'
or dx_code like 'M351'
or dx_code like 'M353'
or dx_code like 'M360'

Is this my best option?
Thanks!

Jim_Knicely

I believe the LIKE ANY operator in Teradata re-writes the query to use the OR operator. for you. Unfortunately in Vertica its a manual process.

Is this any easier?

dbadmin=> select * from dx_code_table;
    dx_code
---------------
 M05555
 M06666
 M315
 M321232
 M33122
 M34123
 M351
 M353
 M360
 NOT THIS ONE!
(10 rows)

dbadmin=> with q as
dbadmin-> (select split_part(c, ',', rn) dx_code from (select 'M05%,M06%,M315,M32%,M33%,M34%,M351,M353,M360' c, row_number() over () rn from all_tables) foo where rn <= regexp_count(c, ',')+1)
dbadmin-> select d.dx_code, nvl2(q.dx_code, 1, 0) LIKE_ANY
dbadmin-> from dx_code_table d
dbadmin-> left join q
dbadmin-> on d.dx_code like q.dx_code;
    dx_code    | LIKE_ANY
---------------+----------
 M05555        |        1
 M06666        |        1
 M315          |        1
 M321232       |        1
 M33122        |        1
 M34123        |        1
 M351          |        1
 M353          |        1
 M360          |        1
 NOT THIS ONE! |        0
(10 rows)

Just replace the string 'M05%,M06%,M315,M32%,M33%,M34%,M351,M353,M360' in the select with what ever string of values you want...

Jim_Knicely

Or using a TEMP table to hold the LIKE ANY values?

dbadmin=> select * from dx_code_table;
    dx_code
---------------
 M05555
 M06666
 M315
 M321232
 M33122
 M34123
 M351
 M353
 M360
 NOT THIS ONE!
(10 rows)

dbadmin=> select * from dx_code_table_temp;
 dx_code
---------
 M05%
 M06%
 M315
 M32%
 M33%
 M34%
 M351
 M353
 M360
(9 rows)

dbadmin=> select d1.dx_code, nvl2(d2.dx_code, 1, 0) LIKE_ANY
dbadmin-> from dx_code_table d1
dbadmin-> left join dx_code_table_temp d2
dbadmin-> on d1.dx_code like d2.dx_code;
    dx_code    | LIKE_ANY
---------------+----------
 M05555        |        1
 M06666        |        1
 M315          |        1
 M321232       |        1
 M33122        |        1
 M34123        |        1
 M351          |        1
 M353          |        1
 M360          |        1
 NOT THIS ONE! |        0
(10 rows)

ablack3

Thanks for the suggestions. I am using the R package dplyr to send SQL to Vertica (which is so sweet by the way) so I think the simplest solution will be to add a custom "like any" operator that generates multiple LIKE statements concatenated with OR. I appreciate your help!

Jim_Knicely

@ablack3 - Thanks for the update! I'm sure your solution will help benefit other community members!

vbagare

This might be very belated but then I thought of sharing it with you anyway.

SELECT
id
,CASE WHEN REGEXP_LIKE(UPPER(cd), 'M2MCNG'|'MOBMOB') THEN ...
ELSE .... END
FROM abc;