PostgreSQL&regexp

本文介绍了PostgreSQL&regexp_split_to_array +嵌套的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我有这样的字符串

测试1 |纽约|X，测试2 |芝加哥| Y，测试3 |宾夕法尼亚州哈里斯堡|Z

Test 1|new york| X, Test 2| chicago|Y, Test 3| harrisburg, pa| Z

我需要的结果是

Column1 Column 2 Column3 Test 1 new york X Test 2 chicago Y Test 3 harrisburg,pa Z

但是运行此查询

SELECT split_part(stat.st, '|', 1) Column1, split_part(stat.st, '|', 2) Column2, split_part(stat.st, '|', 3) Column3 FROM ( SELECT UNNEST ( string_to_array('Test 1|new york| X, Test 2| chicago|Y, Test 3| harrisburg, pa| Z',',') ) AS st ) stat;

结果是

Column1 Column 2 Column3 Test 1 new york X Test 2 chicago Y Test 3 harrisburg pa Z

Column3可以是所有内容(|除外).可能与之匹配的模式.可以重复N次.除|外，STRING可能是所有内容字符

Column3 could be everything (except | ). Possible pattern to match it's .This could be repeated N times. STRING could be everything except | char.

如何使用 regexp_split_to_array()设置我想要的结果?

How could I use regexp_split_to_array() to have my desire result set?

推荐答案

有足够的信息使这些工作正常进行.但这可以完成工作:

There is barely enough information to make this work. But this does the job:

SELECT * FROM crosstab3( $$ SELECT (rn/3)::text AS x, (rn%3)::text, item FROM ( SELECT row_number() OVER () - 1 AS rn, trim(item) AS item FROM ( SELECT CASE WHEN rn%2 = 1 THEN regexp_split_to_table(item, ',') ELSE item END AS item FROM ( SELECT row_number() OVER () AS rn, * FROM regexp_split_to_table('Test 1|new york| X, Test 2| chicago|Y, Test 3| harrisburg, pa| Z', '\|') AS item ) x ) y ) z $$)

返回:

row_name | category_1 | category_2 | category_3 ----------+------------+----------------+------------ 0 | Test 1 | new york | X 1 | Test 2 | chicago | Y 2 | Test 3 | harrisburg, pa | Z

在 | 处分割字符串后，我基于这样的标准:只有行号不均匀的行应在，处分割.我对结果进行 trim()并添加另一个 row_number()的导数，以在进行交叉制表之前达到此中间状态:

After splitting the string at |, I build on the criterion that only lines with uneven row number shall be split at ,. I trim() the results and add derivatives of another row_number() to arrive at this intermediary state before doing the cross tabulation:

x | text | item ---+------+---------------- 0 | 0 | Test 1 0 | 1 | new york 0 | 2 | X 1 | 0 | Test 2 1 | 1 | chicago 1 | 2 | Y 2 | 0 | Test 3 2 | 1 | harrisburg, pa 2 | 2 | Z

最后，我从 tablefunc 模块.要安装它，如果还没有:

Finally, I apply the crosstab3() function from the tablefunc module. To install it, if you haven't already:

CREATE EXTENSION tablefunc;

使用 regexp_replace() 进行预处理

这是一个可能更容易理解的替代方法.不知道哪个更快.复杂的正则表达式往往很昂贵:

Pre-process with regexp_replace()

Here is an alternative that may be easier to comprehend. Not sure which is faster. Complex regular expressions tend to be expensive:

SELECT trim(split_part(a,'|', 1)) AS column1 ,trim(split_part(a,'|', 2)) AS column2 ,trim(split_part(a,'|', 3)) AS column3 FROM ( SELECT unnest( string_to_array( regexp_replace('Test 1|new york| X, Test 2| chicago|Y, Test 3| harrisburg, pa| Z' ,'([^|]*\|[^|]*\|[^,]*),', '\1~^~', 'g'), '~^~')) AS a ) sub

仅在两个管道( | )之后，才替换逗号(，)，然后继续操作.现在使用 * 而不是 + 允许管道之间留空字符串.

This one replaces commas (,) only after two pipes (|), before proceeding. Now using * instead of + to allow for empty strings between the pipes.