Pattern match with CTE

0 votes

1 answer

52 views

                          How do you pattern match using cte?

| id | accountids | email        |
| -- | ---------- | ------------ |
| a12| 456,789,012| jane@foo.com |
| b23| 546,982,123| ben@foo.com  |
| c45| 654,982,456| mike@foo.com |

I've tried following:

    with cte1 as (
    	select '''' || '{' || accountids || '}' || '''' as accountids
    	from table_a
        where email = 'jane@foo.com'
    )
    select id 
    from table_a
    where accountids ^@ ANY (select accountids from cte1);

I would expect result set to return a12 and c45.  Is this possible?
                        

Asked by obautista (437 rep)

Mar 31, 2025, 01:07 PM
Last activity: Mar 31, 2025, 02:09 PM

Pattern match with CTE

Related Questions