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

Comments

  • Hello Ravindra, thanks for your question! We'll see how other customers have transitioned from Teradata and their functions.
  • 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
  • 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_KnicelyJim_Knicely - Select Field - Administrator
    edited December 2017

    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_KnicelyJim_Knicely - Select Field - Administrator
    edited December 2017

    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'

    Is this my best option?
    Thanks!

  • Jim_KnicelyJim_Knicely - Select Field - Administrator
    edited December 2017

    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_KnicelyJim_Knicely - Select Field - Administrator

    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)
    
  • edited December 2017

    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_KnicelyJim_Knicely - Select Field - Administrator

    @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;

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file