Question 89715 in PostgreSQL Question posted in
The official documentation can be found here.

How to extract an uppercase word from a string in Postgresql only if entire word is in capital letters

I am trying to extract words from a column in a table only if the entire word is in uppercase letters (I am trying to find all acronyms in a column). I tried using the following code, but it gives me all capital letters in a string even if it is just the first letter of a word.

SELECT title, REGEXP_REPLACE(title, '[^A-Z]+', '', 'g') AS acronym
FROM table;

Here is my desired output:

title acronym
I will leave ASAP ASAP
David James is LOL LOL
BTW I went home BTW
Please RSVP today RSVP
Tags:

2

Answers


  1. 0

    You could use this regular expression:

    regexp_replace(title, '.*?m([[:upper:]]+)M.*', '1 ', 'g')
    • .*? matches arbitrary letters is a non-greedy fashion
    • m matches the beginning of a word
    • ( is the beginning of the part we are interested in
    • [[:upper:]]* are arbitrarily many uppercase characters
    • ) is the end of that part we are interested in
    • M matches the end of a word
    • .* matches arbitrary characters
    • 1 references the part delimited by the parentheses
    Login or Signup to reply.
  2. 0

    You could try REGEXP_MATCHES function

    WITH data AS 
          (SELECT 'I will leave ASAP' AS title  
UNION ALL SELECT 'David James is LOL' AS title
UNION ALL SELECT 'BTW I went home' AS title
UNION ALL SELECT 'Please RSVP today' AS title)
SELECT title, REGEXP_MATCHES(title, '[A-Z][A-Z]+', 'g') AS acronym
FROM data;

    See demo here

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search