I am looking for a way to split a string into several sub-strings using Postgresql, but the sub-strings are separated by different characters.
For example, the string is like
"Monday Tuesday Wednesday Thursday,Friday,Saturday,Sunday"
The expected result is like
"Monday","Tuesday","Wednesday","Thursday","Friday","Saturday","Sunday"
I have found regexp_split_to_array and string_to_array, but it seems to only handle one character as the delimiter.
regexp_split_to_array (and regex_split_to_table) certainly doe handle different delimiters.
regress=> SELECT regexp_split_to_table('"Monday","Tuesday","Wednesday","Thursday","Friday","Saturday","Sunday"', '[, ]');
regexp_split_to_table
-----------------------
"Monday"
"Tuesday"
"Wednesday"
"Thursday"
"Friday"
"Saturday"
"Sunday"
(7 rows)
Use regexp_split_to_array if you prefer an array, and array_to_string if you want to turn the array back into a delimited string, e.g.
regress=> SELECT array_to_string(regexp_split_to_array('"Monday","Tuesday","Wednesday","Thursday","Friday","Saturday","Sunday"', '[, ]'), ',');
array_to_string
------------------------------------------------------------------------
"Monday","Tuesday","Wednesday","Thursday","Friday","Saturday","Sunday"
(1 row)
Please go read https://dba.stackexchange.com/questions/55871/postgresql-list-of-integers-separated-by-comma-or-integer-array-for-performance ; if you're storing this data in the DB it's probably a design issue you should look at.
Try this Query
SELECT string_agg(value, ',') FROM
(
SELECT regexp_split_to_table('"Monday","Tuesday","Wednesday","Thursday","Friday","Saturday","Sunday"', '[, ]')AS Value
) A
Output:
"Monday","Tuesday","Wednesday","Thursday","Friday","Saturday","Sunday"
regexp_split_to_array and array_to_string?