CODEWITHSUNDEEP
google-bigquery
Sgaddam
Sgaddam

Reputation: 3

Want to search a field value present in another field using big query CONTAINS_SUBSTR

I am trying to use CONTAINS_SUBSTR of big query to find value of field1 present in field2. Eg: Name and Address are 2 fields. I want to search "is name value is present in address field".

select ID, CONTAINS_SUBSTR(address,name)
from (SELECT "Rama" as Name,"Rama;HS121;HYD;IND" as address
union all
SELECT "123" as ID ,"Rama" as Name,"Rama;HS121;HYD;IND" as address union all
SELECT "124" as ID ,"Sita" as Name,"Sita;HS131;HYD;IND" as address union all
SELECT "125" as ID ,"Abc" as Name,"Rama;HS161;HYD;IND" as address union all
SELECT "126" as ID ,"xyz" as Name,"Sita;HS171;HYD;IND" as address union all
SELECT "127" as ID ,"aaa" as Name,"HS181;HYD;IND" as address
)

getting error: Argument 2 to DREMELGOOGLESQL:CONTAINS_SUBSTR must be a literal or query parameter at

expected: I want to extract ID- 123,124 but i am not able to pass field as 2nd argument for CONTAINS_SUBSTR

is there any alternative way for this ? to identify one field value present in another field ?

Upvotes: 0

Views: 1248

Answers (2)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172984

Or you can use REGEXP_CONTAINS(address,name) instead of CONTAINS_SUBSTR(address,name) as in below

select ID, REGEXP_CONTAINS(address,name)
from (
  SELECT "111" as ID, "Rama" as Name,"Rama;HS121;HYD;IND" as address union all
  SELECT "123" as ID ,"Rama" as Name,"Rama;HS121;HYD;IND" as address union all
  SELECT "124" as ID ,"Sita" as Name,"Sita;HS131;HYD;IND" as address union all
  SELECT "125" as ID ,"Abc" as Name,"Rama;HS161;HYD;IND" as address union all
  SELECT "126" as ID ,"xyz" as Name,"Sita;HS171;HYD;IND" as address union all
  SELECT "127" as ID ,"aaa" as Name,"HS181;HYD;IND" as address
)

Upvotes: 1

Mohammad
Mohammad

Reputation: 678

Try this:

WITH RAW_DATA AS 
(SELECT "128" AS ID, "Rama" as Name,"Rama;HS121;HYD;IND" as address union all
SELECT "123" as ID ,"Rama" as Name,"Rama;HS121;HYD;IND" as address union all
SELECT "124" as ID ,"Sita" as Name,"Sita;HS131;HYD;IND" as address union all
SELECT "125" as ID ,"Abc" as Name,"Rama;HS161;HYD;IND" as address union all
SELECT "126" as ID ,"xyz" as Name,"Sita;HS171;HYD;IND" as address union all
SELECT "127" as ID ,"aaa" as Name,"HS181;HYD;IND" as address
)

select ID,Name,address from RAW_DATA
WHERE address LIKE  '%'||Name||'%';

Expected Output:

enter image description here

Upvotes: 0

Related Questions