Below example is for BigQuery Standard SQL and explains your problem

#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`    

with output

enter image description here

So, as you can see – non-breaking space is not recognized as a space character or any white space

Forgot to mention possible solution –

#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` 

with output

enter image description here

CLICK HERE to find out more related problems solutions.

Leave a Comment

Your email address will not be published.

Scroll to Top