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.
  • Sajan_SangraulaSajan_Sangraula Registered User
    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
  • ablack3ablack3 Registered User

    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 Employee, Registered User, VerticaExpert
    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 Employee, Registered User, VerticaExpert
    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)
    
  • ablack3ablack3 Registered User

    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 Employee, Registered User, VerticaExpert
    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 Employee, Registered User, VerticaExpert

    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)
    
  • ablack3ablack3 Registered User
    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 Employee, Registered User, VerticaExpert

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

Leave a Comment

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