string match and remove each row in a postgresql-CodePudding

So in short:

I need to find all rows in the column Translation that begin with the letter M (M123 is one of the Prefixes) and then I need to remove the M123 and similar prefixes from all the rows.

For example, row 1 contains the following data: M123 - This is translated from Spanish to English

And I need to remove the M123 - from the mentioned data. And this I need to do for the Translation column in every row in the table.

It's been a while since I actually did some SQL-Queries. So I tried a WHERE clause to find all the M prefixes but my query returns an empty search. Following is the query I am using atm:

SELECT Translation from Translation_Table where Translation like 'M';

I am a little bit confused right now. So any help is appreciated.

CodePudding user response：

I sense that you might be wanting to do an update here, rather than a select:

UPDATE Translation_Table
SET Translation = REGEXP_REPLACE(Translation, 'M[0-9] ', '')
WHERE Translation ~ '^M[0-9] ';

CodePudding user response：

addition to this answer following query will remove all occurence of M[any length of a number]

UPDATE Translation_Table
SET Translation = REGEXP_REPLACE(Translation, '[M[:digit:]]', '', 'g')
WHERE Translation ~ '.M[0-9]*';