How to remove empty words from my SQL string_to_array?

Question

I have a text column in my Postgres table and I want to remove any non-alphabetical characters and split it by a space (so I can use each word to do a search later).

I can remove the characters and split it successfully however my rows have empty results. I cannot have any empty words:

SELECT
    asset.id,
    string_to_array(TRIM(regexp_replace(asset.title, '[^a-zA-Z ]', '', 'g')), ' ')
FROM
    assets asset

eg.
Hello world! becomes {Hello,world}
but also Some Result V1.0 - 3D Model becomes {Some,Result,V,,D,Model

How do I filter my array to remove empty words?

Pavel Stehule · Accepted Answer · 2022-06-20 05:25:47Z

2

You can transform array to table, then clean the content, and transform result to array again:

CREATE OR REPLACE FUNCTION public.remove_empty_string(text[])
 RETURNS text[]
 LANGUAGE sql
AS $function$
select array(select v from unnest($1) g(v) where v <> '')
$function$

(2022-06-20 05:17:57) postgres=# select remove_empty_string(array['Some','Result','V','','D','Model']);
┌─────────────────────────┐
│   remove_empty_string   │
╞═════════════════════════╡
│ {Some,Result,V,D,Model} │
└─────────────────────────┘
(1 row)

edited Jun 20, 2022 at 5:25

answered Jun 20, 2022 at 3:19

Pavel Stehule

46.6k6 gold badges103 silver badges102 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Jared Over a year ago

(Scratch last comment) This works the best as it guarantees you do not get empty items in the final array. I did have to change the return type to TEXT[] though

Pavel Stehule Over a year ago

@Jared - yes, there was a bug, fixed

codeRookieErick · Accepted Answer · 2022-06-20 03:35:42Z

1

Try using a multicharacter regular expression

Try with this:

SELECT
    asset.id,
    string_to_array(TRIM(regexp_replace(asset.title, '[^a-zA-Z]+', ' ', 'g')), ' ')
FROM
    assets asset

I just removed the whitespace from the regex in order to capture every non-alphabet character. Then, the + indicates one or more coincidences, which ensures than any consecutive non-alphabetical character will get replaced with a single space. Finally, as you already do the trim, your split by whitespace will work.

edited Jun 20, 2022 at 3:35

answered Jun 20, 2022 at 3:22

codeRookieErick

363 bronze badges

2 Comments

Jared Over a year ago

Thanks for the answer. I combined yours with the accepted one as I still had empty strings inside my array.

codeRookieErick Over a year ago

@Jared Your welcome. I'm glad it worked at least partially for you.

Collectives™ on Stack Overflow

How to remove empty words from my SQL string_to_array?

2 Answers 2

2 Comments

Try using a multicharacter regular expression

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Try using a multicharacter regular expression

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related