CODEWITHSUNDEEP
mysqlsql-serveroracle11ggoogle-bigquery
Faical
Faical

Reputation: 45

Table values as input for a SQL function

I am trying to use all the values of a table as an input for an SQL defined function. 

SELECT Field1,  REGEXP_CONTAINS(Field1, (SELECT Field2 FROM table2)) FROM table1;

This one returns an error in big query because:

  1. the sub-query can't be used as an input and
  2. it's returning more than 1 row.

Can you please tell me if there is a way to use all the values of one table as an input for a single function.

Best regards,

Upvotes: 1

Views: 420

Answers (3)

Faical
Faical

Reputation: 45

Thank you all for your answers. I have a similar version to big query and ended up using the following code which is close to your suggestions. 

WITH table1 AS (SELECT field1 FROM UNNEST(['value1', ...'value2']) AS field1),
table2 AS (SELECT field2 FROM UNNEST(['v1', 'v2']) AS field2)
SELECT
field1,
(SELECT LOGICAL_OR(REGEXP_CONTAINS(field1, CONCAT(r'(?i)\b', field2, r'\b'))) FROM table2) AS has_table2_match
FROM
table1

Thank you again for your suggestions. Appreciate it.

Upvotes: 0

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172993

Below example is for BigQuery Standard SQL 

#standardSQL
SELECT 
  Field1, STRING_AGG(Field2) AS contained
FROM `table1`
JOIN `table2`
ON REGEXP_CONTAINS(Field1, Field2) 
GROUP BY Field1

you can test / play with above using below dummy data 

#standardSQL
WITH `table1` AS (
  SELECT 'abc' Field1 UNION ALL
  SELECT 'xyz'
),
`table2` AS (
  SELECT 'a' Field2 UNION ALL
  SELECT 'x' UNION ALL
  SELECT 'y' UNION ALL
  SELECT 'z'
)
SELECT 
  Field1, STRING_AGG(Field2) AS contained
FROM `table1`
JOIN `table2`
ON REGEXP_CONTAINS(Field1, Field2) 
GROUP BY Field1

with result as 

Field1  contained    
abc     a    
xyz     x,y,z

Second Option is more inline with your original expectation (in terms of syntax pattern) but requires use of SQL UDF:

#standardSQL
CREATE TEMP FUNCTION Check_Contains(str STRING, arr ARRAY<STRING>) AS ((
  SELECT STRING_AGG(item) AS contained
  FROM UNNEST(arr) item
  WHERE REGEXP_CONTAINS(str, item) 
));
SELECT Field1, 
  Check_Contains(Field1, ARRAY(SELECT Field2 FROM `table2`)) AS contained
FROM `table1`

To test / play - you can use below 

#standardSQL
CREATE TEMP FUNCTION Check_Contains(str STRING, arr ARRAY<STRING>) AS ((
  SELECT STRING_AGG(item) AS contained
  FROM UNNEST(arr) item
  WHERE REGEXP_CONTAINS(str, item) 
));
WITH `table1` AS (
  SELECT 'abc' Field1 UNION ALL
  SELECT 'xyz' UNION ALL
  SELECT 'vwu'
),
`table2` AS (
  SELECT 'a' Field2 UNION ALL
  SELECT 'x' UNION ALL
  SELECT 'y' UNION ALL
  SELECT 'z'
)
SELECT Field1, 
  Check_Contains(Field1, ARRAY(SELECT Field2 FROM `table2`)) AS contained
FROM `table1`

with output as 

Field1  contained    
abc     a    
xyz     x,y,z    
vwu     null

One more option (without UDF) 

#standardSQL
SELECT Field1, 
  ARRAY_TO_STRING(REGEXP_EXTRACT_ALL(Field1, (SELECT STRING_AGG(Field2, '|') FROM `table2`)), ',') AS contained
FROM `table1`

you can use same dummy data as for above (first two options)

Upvotes: 2

Elliott Brossard
Elliott Brossard

Reputation: 33745

You need to use a CROSS JOIN instead. Note that this will be slow if the tables are large, since you're taking the cross product of all the rows:

SELECT
  t1.Field1,
  REGEXP_CONTAINS(t1.Field1, t2.Field2)
FROM table1 AS t1
CROSS JOIN table2 AS t2;

If you want to know all of the Field1/Field2 pairs for which this expression is true, you can use a query with a filter:

SELECT
  t1.Field1,
  t2.Field2
FROM table1 AS t1
CROSS JOIN table2 AS t2
WHERE REGEXP_CONTAINS(t1.Field1, t2.Field2);

Upvotes: 1

Related Questions