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

Postgresql – Split a string into an array and trim each element

I’ve worked out how to split a string into an array, but I’m not sure how I can use a trim function on each element to remove any whitespace.

Right now, when I select and separate the field, I use the following syntax (where ";" is the delimiter):

string_to_array("Column", ';') as "SplitColumn"

But if "Column" contains "something ; like ; this", the output would be

"SplitColumn":["something ", " like ", " this"]
Tags:

4

Answers


  1. 0

    You can access each elements of the array, trim them, and build back the array.
    Don’t forget the inner select.

    select array(select trim(unnest(array[' a ',' b ',' c '])));
 array
---------
 {a,b,c}
    Login or Signup to reply.
  2. 0

    You can break the input string into an array using the string_to_array function and the ; delimiter. Leading and trailing whitespace will be used to generate an array with entries that contain them. The array_to_string function can then be used to remove the whitespace from each entry. This function removes any excess whitespace before concatenating the array’s items into a single string. Just like in the example I am mentioning

    SELECT array_to_string(string_to_array("Column", ';'), ';') as "CleanedColumn"
FROM your_table;

    Using the ; delimiter, this query separates the input string into an array, cleans up each element by removing any whitespace, and then puts the array back together. You’ll get a string without any whitespace.
    Hope this helps 🙂

    Login or Signup to reply.
  3. 0

    You can achieve this with the TRIM function along with the string_to_array() function;

    SELECT
  TRIM(BOTH ' ' FROM UNNEST(string_to_array("Column", ';'))) AS "TrimmedSplitColumn"
FROM
  your_table;
    Login or Signup to reply.
  4. 0

    This should work:

    SELECT
  ARRAY(
    SELECT TRIM(both ' ' FROM unnest(string_to_array("Column", ';')))
  ) as "SplitColumn"
FROM your_table;

    string_to_array("Column", ‘;’) splits the "Column" into an array using ‘;’ as the delimiter. unnest turns the array into a set of rows and wrapping it in Array would convert it back to an array.

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

Back To Top
Search