BigQuery中Update语句多换行Where子句的处理方法

我在BigQuery执行Update语句时，需要精准匹配identifier字段中一段包含换行、空格、双引号的长文本。用反斜杠转义引号后，换行和空格还是无法正确匹配，导致语句失效。因为必须精准匹配原字段值，不能修改Where子句的匹配逻辑。

原Update语句：

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"

我试过用LIKE做模糊匹配，但字段里有大量相似值，无法精准定位目标行：

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

有没有不用修改表列（移除非字母数字字符）的解决办法？

解决方法 #

不需要修改表列，用以下两种方式即可实现精准匹配：

1. 使用BigQuery原始字符串字面量

BigQuery支持用r""（双引号包裹）或r''（单引号包裹）定义原始字符串，里面的特殊字符（包括双引号、换行、空格）不需要转义，会完全保留原始格式。修改后的语句如下：

UPDATE dataset.datatable 
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"

注意：原始字符串里的单引号如果是用r""包裹的，不需要转义；如果用r''包裹，单引号需要写成两个连续单引号（''）。

2. 手动转义换行符

如果不想用原始字符串，可以把原文本中的换行替换成BigQuery识别的\n转义字符，同时正确转义引号：

UPDATE dataset.datatable 
SET project = "something" 
WHERE identifier="The $1.90M adajdshsas ahsdsjahd adsad add adddadcxv ksdf sdfsd sdfsff.\n\nadjajasd by ahsdhasf kkjfdsg \"kasf the new uenf will\" ensure #maidsbd and surrounding communities will have greater access to #madaudafvys support and sajdahdj locally.\n\nThe new centre was funded through the asdashdgad jsfjfsfuu kasahfa(hsf).\n\nRead more: asdgsa./asdga\n\n#hasdad #sadauyd\n\nasdahda, asdad\'s dshag, minadahdh"

这种方式需要确保所有换行都替换成\n，空格和其他特殊字符保留原样，引号按规则转义。

内容的提问来源于stack exchange，提问作者jelly