以下示例适用于 BigQuery 标准 SQL,并解释了您的问题
#standardSQL
with `project.dataset.table` as (
select 'with spaces' space_type, '8 800 000 kr' slutpris union all
select 'with non-breaking spaces', replace('8 800 000 kr', chr(32), chr(160)) slutpris
)
select space_type, slutpris,
replace(slutpris, ' ', ''),
regexp_replace(slutpris, r'\s', ''),
regexp_replace(slutpris, r'\s|kr', '')
from `project.dataset.table`
带输出
因此,正如您所看到的 - 不间断空格不会被识别为空格字符或任何空格
忘记提及可能的解决方案 -
#standardSQL
with `project.dataset.table` as (
select 'with spaces' space_type, '8 800 000 kr' slutpris union all
select 'with non-breaking spaces', replace('8 800 000 kr', chr(32), chr(160)) slutpris
)
select space_type, slutpris,
translate(slutpris, chr(32) || chr(160), ''),
regexp_replace(slutpris, '[\u00A0\\s]', ''),
regexp_replace(slutpris, '[\u00A0\\s]|kr', '')
from `project.dataset.table`
带输出