如果模式被连字符和斜杠分开,则可以使用带有regex_split_to数组(https://www.postgresql.org/docs/current/functions-string.html#id-1.5.8.9.7.2.2.29.1.1)的正则表达式,像这样
SELECT
split.array[1] as col1,split.array[2] as col2,split.array[3] as col3
FROM (
SELECT regexp_split_to_array('30-20/0.5','-|\/') AS array
) AS split;
只需将字符串替换为对表和列的查询即可。
要对数组进行排序,您将需要创建一个自定义函数来对数组进行排序。
CREATE OR REPLACE FUNCTION array_sort_desc(ANYARRAY)
RETURNS ANYARRAY LANGUAGE SQL
AS $$
SELECT ARRAY(
SELECT $1[s.i] AS col
FROM generate_series(array_lower($1,1),array_upper($1,1)) AS s(i)
ORDER BY col DESC
);
$$;
这样您就可以像这样订购
SELECT
split.array[1] as col1,split.array[3] as col3
FROM (
SELECT array_sort_desc(regexp_split_to_array(your_column,'[-/]')::numeric[]) AS array
FROM your_table
) AS split;
如果元素少于3个,则最右边的项目将为空。
,
Click: demo:db<>fiddle
SELECT
a[1] as col1,a[2] as col2,COALESCE(a[3],0) as col3
FROM (
SELECT
(regexp_split_to_array(values,'[-/]'))::decimal[] as a
FROM
mytable
) s
-
regexp_split_to_array
在给定的定界符的帮助下拆分字符串。在这种情况下,-
或/
- 可选:
(array)::decimal[]
将字符串数组转换为数字数组
-
a[X]
给出数组的第X个元素
-
COALESCE(a[3],0)
如果不存在第三部分(而不是0
),则给出null
本文链接:https://www.f2er.com/2971554.html