How to handle multiple lines in a where clause for an update query (BigQuery)

Question

I am trying use an update query which has a multiple line where clause. It contains inverted commas, space and newline. The backslash works for the inverted commas but not able to handle space and empty lines in the clause. As it is an update where clause, I won't be able to change the structure of the clause otherwise it won't match with the existing value.

UPDATE dataset.datatable 
SET project = "something" 
WHERE identifier="The $1.90M adajdshsas ahsdsjahd adsad add adddadcxv ksdf sdfsd sdfsff.  

adjajasd by ahsdhasf kkjfdsg \"kasf the new uenf will\" ensure #maidsbd and surrounding communities will have greater access to #madaudafvys support and sajdahdj locally. 

The new centre was funded through the asdashdgad jsfjfsfuu kasahfa(hsf).  

Read more: asdgsa./asdga 

#hasdad #sadauyd 

asdahda, asdad\'s dshag, minadahdh"

I have tried using like instead, However, there are too many similar values in the column.

UPDATE dataset.datatable 
SET project = "something" 
WHERE identifier LIKE "%The $1.90M%" AND identifier LIKE "%\"kasf the new uenf will\"%"

Is there a way to handle this situation, or do I have to modify the column in the table by removing all the non alphanumerical characters?

Answer 1

Below example demonstrates the approach

CREATE TEMP TABLE your_table as (
SELECT 1 id, "abc12345678" project, r"""The $1.90M adajdshsas ahsdsjahd adsad add adddadcxv ksdf sdfsd sdfsff.  

adjajasd by ahsdhasf kkjfdsg \"kasf the new uenf will\" ensure #maidsbd and surrounding communities will have greater access to #madaudafvys support and sajdahdj locally. 

The new centre was funded through the asdashdgad jsfjfsfuu kasahfa(hsf).  

Read more: asdgsa./asdga 

#hasdad #sadauyd 

asdahda, asdad\'s dshag, minadahdh""" identifier UNION ALL
SELECT 2, "xyz876543", "qwertyu"

);
 
UPDATE  your_table
SET project = "something" 
WHERE  identifier = r"""The $1.90M adajdshsas ahsdsjahd adsad add adddadcxv ksdf sdfsd sdfsff.  

adjajasd by ahsdhasf kkjfdsg \"kasf the new uenf will\" ensure #maidsbd and surrounding communities will have greater access to #madaudafvys support and sajdahdj locally. 

The new centre was funded through the asdashdgad jsfjfsfuu kasahfa(hsf).  

Read more: asdgsa./asdga 

#hasdad #sadauyd 

asdahda, asdad\'s dshag, minadahdh""" ;

select * from your_table;      

In above example - your_table before update is

and after update it is