string match and remove each row in a postgresql

Question

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.

your question is a bit confusing! you want to find all rows where Translation starts with M and then remove it from Translation, am I right?? how about column Translation is it holding only M123 or M123 something or something M123 something or in every column?? — ANIK ISLAM SHOJIB
– ANIK ISLAM SHOJIB, Commented Jul 6, 2022 at 9:26
can you share the structure of your table?? and you said I need to do it for every row in the table what do you mean by this, changing the Translation column in every row?? — ANIK ISLAM SHOJIB
– ANIK ISLAM SHOJIB, Commented Jul 6, 2022 at 10:02
Changing the data in the every row of the Translation column where the prefix M123 or similar is found. — Malgosh
– Malgosh, Commented Jul 6, 2022 at 10:07
This does not directly address the question. But an observation you need to realize and keep in mind. When you use a predicate of the form string like pattern where the pattern does not contain a wildcard character (_%) is equivalent to the predicate string = pattern. So in this case where Translation like 'M' is the same as where Translation = 'M'; — Belayer
– Belayer, Commented Jul 7, 2022 at 2:00

Tim Biegeleisen · Accepted Answer · 2022-07-06 09:24:05Z

1

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]+';

answered Jul 6, 2022 at 9:24

Tim Biegeleisen

526k32 gold badges323 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

ANIK ISLAM SHOJIB · Accepted Answer · 2022-07-06 12:29:14Z

0

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]*';

answered Jul 6, 2022 at 12:29

ANIK ISLAM SHOJIB

3,3383 gold badges31 silver badges48 bronze badges

Collectives™ on Stack Overflow

string match and remove each row in a postgresql

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related