PostgreSQL - Split words (len>1) into rows with regexp_split_to_table

Question

I'm trying to split the following string into rows such that every word is in its own row with the exception of words of a single character (e.g. a, i):

"the quick brown fox jumps over a lazy dog"

This is what I've been messing with in SQL Fiddle, but I can't get the right result:

SELECT foo FROM regexp_split_to_table('the quick brown fox jumps over a lazy dog',  
  E'(\\s+)(?=\\w{2,})') AS foo;

The result:

the
quick
brown
fox
jumps
over a
lazy
dog

It's almost correct, but for some reason the a is being combined with over. I want to disregard the a completely.

What's the little thing I'm missing?

SELECT foo FROM regexp_split_to_table('the quick brown fox jumps over a lazy dog', E'(?<=\\w{2,}) (?=\\w{2,})') AS foo;? — Abdou
– Abdou, Commented Oct 25, 2017 at 16:33
select regexp_split_to_table('the quick brown fox jumps over a i lazy dog', '\s+(\w\s+)*'); — Abelisto
– Abelisto, Commented Oct 25, 2017 at 17:30
I'm working with version 9.6 and that may just be the reason. I will delete my answer and urge @Abelisto to write his or hers. — Abdou
– Abdou, Commented Oct 25, 2017 at 17:38
I'm also working with 9.6, so I'm unsure what the issue is. I appreciate your help regardless! — Lance_P
– Lance_P, Commented Oct 25, 2017 at 17:40

Abelisto · Accepted Answer · 2017-10-25 20:17:50Z

The one solution already provided in the comment:

select regexp_split_to_table('the quick brown fox jumps over a i lazy dog.', '\s+(\w\s+)*');

However it does not takes in account punctuation marks. So instead of splitting the string by delimiter(s) you cant to extract words from it using regexp_matches() function:

postgres=# select (regexp_matches('the quick brown fox;jumps over, a lazy dog.', '(\w{2,})', 'g'))[1];
-- or
postgres=# select unnest(regexp_matches('the quick brown fox;jumps over, a lazy dog.', '(\w{2,})', 'g'));
┌────────────────┐
│ regexp_matches │
╞════════════════╡
│ the            │
│ quick          │
│ brown          │
│ fox            │
│ jumps          │
│ over           │
│ lazy           │
│ dog            │
└────────────────┘

Collectives™ on Stack Overflow

PostgreSQL - Split words (len>1) into rows with regexp_split_to_table

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related