BQ function to transform caron character from a string to character without caron

Question

I am trying to get rid of a caron characters (č,ć,š,ž) and transform them to c,c,s,z :

with my_table as (
    select 'abcčd sštuv' caron, 'abccd sstuv' no_caron union all
    select 'uvzž', 'uvzz' union all
    select 'ijkl cČd', 'ijkl cCd' union ALL
    select 'Ćdef', 'Cdef'
)
SELECT *
FROM my_table

I was trying to get rid of them with

SELECT *,
    regexp_replace(caron, r'š', 's') as no_caron
FROM my_table

but I think this is inefficient. I know there is an option to write your on function as described here, but I have no idea how to use it in my case.

Thanks in advance!

Answer 1

Use below

SELECT *, regexp_replace(normalize(caron, NFD), r"\pM", '') output
FROM my_table      

if applied to sample data in your question - output is