Reg Expression for negative lookahead in Big Query

[BeaWeso]

Hello, 

from a query like '

--select * from vf-de-datahub.schema1.table1

select *

from vf-de-datahub.schema2.table2;

--select * from vf-de-datahub.schema3.table3

--select * from vf-de-datahub.schema4.table4

select * from INFORMATION_SCHEMA.JOBS_TIMELINE'

I want to select only schema and table names which belong to the project 'vf-de-datahub' and only from rows which are not commented out.

For my example the proper result should be: schema2.table2

I've prepared the reg expresion, see below, but it takes also the outcommented lines and provides schema1.table1, schema2.table2, schema3.table3, schema4.table4

Do you have any idea how to improve the pattern so that it provides the proper result : schema2.table2

This is my pattern:

WITH b AS (

SELECT 'a' AS query_id, """--select * from vf-de-datahub.schema1.table1

select *

from vf-de-datahub.schema2.table2;

--select * from vf-de-datahub.schema3.table3

--select * from vf-de-datahub.schema4.table4

select * from INFORMATION_SCHEMA.JOBS_TIMELINE""" AS query

)

SELECT query_id,REGEXP_EXTRACT_ALL(query, 'vf-de-datahub[a-zA-Z0-9_.-]+') AS Object

FROM b

Thank you so much, 

Beata

[jaia]

Hello,

Thank you for contacting Google Cloud Community!

Here's the improved regular expression:

WITH b AS (
SELECT 'a' AS query_id, """--select * from vf-de-datahub.schema1.table1
select *
from vf-de-datahub.schema2.table2;
--select * from vf-de-datahub.schema3.table3
--select * from vf-de-datahub.schema4.table4
select * from INFORMATION_SCHEMA.JOBS_TIMELINE""" AS query
)
SELECT query_id, REGEXP_EXTRACT_ALL(query, r'(?

Regards,
Jai Ade

[BeaWeso]

Good Morning Jai, 

thank you. Can you please complete the pattern? It is cut in the middle.

Thanks, 

Beata

[BeaWeso]

I guess, you want to use a negative look ahead but this reg exp is running in Big Query.

Error: Cannot parse regular expression: invalid perl operator: (?!

Big Query doesn't provide ''look ahead' in this syntax.

Is there any other functionalty for negative look ahead for big query?

Thanks, 

Beata