Change case of Acronyms in PostGres

Question

I have a table containing almost 1 million points (POIs). I want to change the case of all the acronyms in the display name column I have, currently I am identifying the acronyms as any 3 letter words that are present in the column ( I know I'll have to filter the results a lot as well ).

I am currently using this query to identify the unique words that are present in the column:

select distinct SUBSTRING(name_displ FROM '([A-Z]{1}[a-zA-Z]{2}\s)') As acronym
from poi

This query is returning all the unique 3 letter words that appear in the start and the middle of the name_displ column, now I want to change these to uppercase, I know that there is the replace(string text, from text, to text) function and the regexp_replace(source, pattern, replacement [, flags ]) function but I'm not sure how I can use them here.

So is there another way to identify 3 letter Acronyms and then change them to uppercase in PostgreSQL?

Answer 1

My first attempt on this topic will be something like this.

UPDATE poi SET name_displ = regexp_replace(name_displ, '([A-Z]{1}[a-zA-Z]{2}\s)', upper('\1'));

Where the key points are:

• When you want to make a "substitution" you must do an UPDATE query
• To get the text match by the regexp you use \1. This is you can use '\1' to use in the substitution the text the part of the text in the regexp that match your first "()". Or you can use '\&' to reuse the whole value matched by the regexp.
• function upper is used to uppercase the matched part of the text

But this is NO WORKING. Reading a bit more \& can not be reused within other functions. So is needed to make a procedure as is explained in this question.

Answer 2

I tweaked the function @Franciso Puga provided a bit, keeping in mind what he said upper('\1') was supposed to do and got it to work!

Used this query:

UPDATE poi SET name_displ = regexp_replace(name_displ, '([A-Z]{1}[a-zA-Z]{2}\s)', upper(SUBSTRING(name_displ FROM '([A-Z]{1}[a-zA-Z]{2}\s)')))