1

I don't think I drank enough coffee today, but stuck on this for some reason.

Trying to get the first letter of each word, except a few stopwords.

Example "The United States of America" -> "USA"

select regexp_matches('The United States of America', '\y(?!(the|of)\y)\w', 'gi')

works fine, but it returns setof text[] .

How to convert that to a simple string? Or is there a better way than regexp_matches?

2 Answers 2

3

Some options:

-- regexp_matches
SELECT string_agg(arr[1], '') AS string
FROM   regexp_matches('The United States of America', '\y(?!(the|of)\y)\w', 'gi') arr;

-- regexp_split_to_table
SELECT string_agg(left(word, 1), '')
FROM   regexp_split_to_table('The United States of America', '\s+') t(word)
WHERE  NOT (word ILIKE ANY ('{the,of}'::text[]));

-- Without regular expression
SELECT string_agg(left(word, 1), '')
FROM   unnest(string_to_array('The United States of America', ' ')) t(word)
WHERE  NOT (word ILIKE ANY ('{the,of}'::text[]));

SQL Fiddle.

Option 2 splits on any amount of white space. Not exactly the same as your regex.
Option 3 splits on a single space character. If you can rely on that (?), the query is cheaper.

1
  • Going to go with option 1, as it's the simplest. Thanks! Aug 26, 2014 at 17:27
1
SELECT REPLACE(array_to_string(array(select array_to_string(regexp_matches('The United States of America', '\y(?!(the|of)\y)\D', 'gi'),'')),''),' ','');

FIDDLE

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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