we have ranges like this
"0,5, 0,5"
"0,112, 0,118"
and want to split by the second comma.
Any idea?
-
How should the result look like?GMB– GMB2020-12-10 09:48:23 +00:00Commented Dec 10, 2020 at 9:48
Add a comment
|
4 Answers
You can update the regex you split by with comma then a space after.
select regexp_split_to_array('0,112, 0,118', ', ')
Comments
Use regexp_replace:
select regexp_replace('0,112, 0,118', '.*,\s+', '') as foo;
Output:
foo
-------
0,118
(1 row)
Comments
Supposing, there ist always at least one space after the second comma and none after the others, you could use this for the split regex:
SELECT
regexp_split_to_array(ranges, ',\s+')
FROM
t
This returns an array like {"0,5","0,5"}.
You can split both ranges into columns using a subquery:
SELECT
r[1],
r[2]
FROM (
SELECT
regexp_split_to_array(ranges, ',\s+') as r
FROM
t
) s
Edit:
TO wants to get everything after the second comma. So you need a regex for splitting, which finds the nth (here n = 2) occurrence of a comma:
(?:(^.*?,.*)),
This can be used to query the required data:
SELECT
(regexp_split_to_array(ranges, '(?:(^.*?,.*)),'))[2]
FROM
t
4 Comments
Bodo
Maybe my first message was too short. I have a column „A“ with the following entries – two values concatenated as string like ranges:
"1,6, 12,1“ "0,333 0,4“ "0,010, 0,010" I would like to get the last value (after the second comma and withouth the following spaces). Result would look like this: " 12,1“ "0,4“ "0,010" I try to do this in postgres.
S-Man
Your second example has no comma between 333 and 0, is that correct?
S-Man
Extended the answer.
Timur Shtatland
@Bodo Please update your question. This way, the exact question will be seen easily, rather than hidden in the comments.
Thank you all for the quick answers. It finally worked by using this
regexp_matches(your_string_value, '\d+[,|.]\d+|\d+','g'))[1]
This helped me getting rid of all unnecessary characters within the values + delivered me back the second value in the range.