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
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)
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'
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!
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?
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?
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;