3

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?

5

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.

2
  • I understand what you are trying to say with the first and the third point but I don't really understand the upper(\1) part. Incidentally, that is also where PostgreSQL gives me an error. – Hasan Mustafa Mar 24 '16 at 12:43
  • It should be upper('\1') but anyway this is not working read the explanation and a way to do it here stackoverflow.com/questions/18698760/… – Francisco Puga Mar 24 '16 at 12:48
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)'))) 

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.