What is the closet function to LIKE ANY in Teradata for Vertica?
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
0
Comments
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.
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);
Actually, since you are using LIKE ANY as a column, this could work:
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!
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...
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)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!
@ablack3 - Thanks for the update! I'm sure your solution will help benefit other community members!
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;