Delete all numbers from Columns in Big Query-CodePudding

Fairly new to SQL and I'm trying to clean up a table I have. I have the following genres Column:

What I'd like to do is permanently delete all numbers from the column so it returns something like

'Action', 'Thriller', Drama 'Tv Movie', 'Action', 'Horror', Science Fiction etc.

I've been trying to write a query to replace but nothing seems to kill all numbers:

Here's an example:

UPDATE `movies-dataset.movies_data.Movies_metadata`
SET genres = REPLACE(genres, '[0-1000]', '')
WHERE TRUE;

My column is a String

If you also think there's an easier way to delete all the unnecessary characters instead of creating a query for each one, that'll be helpful as well.

Ex: have these as well I'd like to remove. I'm just going to write a query to replace them [, :

Thanks!

CodePudding user response：

What you are searching for is REGEXP_REPLACE

This uses regular expalre4ssion to detect the nubers and remove them.

UPDATE `movies-dataset.movies_data.Movies_metadata`
SET genres = REGEXP_REPLACE(genres, '[0-9] ', '')
WHERE TRUE;

like any update you should test this prior to running this on your production servce