如何获取数字在帖子选择中第一次出现的位置

Question

我有一个字段，其中包含SDK名称和版本，并且它没有标准化，因此我可以轻松地仅提取名称和版本。这些是人为的值，但代表我正在使用的可能值：

• JavaScript/2.3.4
• JavaScript/4.3.1
• 安卓4.6.5
• 安卓3.2.1
• 雨燕4.5.3
• 迅捷/3.1.1.5

如您所见，"/"的使用不一致，我需要一种方法来一致地解析数字中的字母，以便我最终得到这样的两列：

JavaScript     2.3.4
JavaScript     4.3.1
Android        4.6.5
Android        3.2.1
Swift          4.5.3
Swift          3.1.1.5

我已经寻找了不同的方法来做到这一点，但我发现似乎没有什么能满足我的要求。

最终，我需要将其放入Postgres SELECT语句中，如下所示：

编辑

我认为 DISTINCT 关键字是不必要的和令人困惑的。它最初是 COUNT/GROUP BY 查询的一部分，但为了简单起见，我只想分别列出包含三列的所有行：sdk、sdk_name 和 sdk_version。从那里，我将使用答案中最好的解析公式来按照我的要求做。

SELECT sdk, [parse sdk name formula] as "sdk_name", [parse sdk version formula] as "sdk_version"

此外，我没有要在查询中提供的固定 SDK 列表，因此我不确定with/as/values策略是否适合我，但我不知道并且看起来很有用。不过，我想 with/values 可能只是另一个 SELECT 查询。

Answer 1

使用正则表达式函数substring()：

with my_data(sdk) as (
values
('JavaScript/2.3.4'),
('JavaScript/4.3.1'),
('Android4.6.5'),
('Android3.2.1'),
('Swift4.5.3'),
('Swift/3.1.1.5')
)
select 
substring(sdk from '[^d/]*') as sdk_name, 
substring(sdk from 'd.*') as sdk_version
from my_data 
sdk_name  | sdk_version 
------------+-------------
JavaScript | 2.3.4
JavaScript | 4.3.1
Android    | 4.6.5
Android    | 3.2.1
Swift      | 4.5.3
Swift      | 3.1.1.5
(6 rows)

---

更新。

您可以将select查询放在with部分(而不是values(：

with my_data(sdk) as (
<select sdk from ...>
)
select 
substring(sdk from '[^d/]*') as sdk_name, 
substring(sdk from 'd.*') as sdk_version
from my_data 

或在from条款中：

select 
substring(sdk from '[^d/]*') as sdk_name, 
substring(sdk from 'd.*') as sdk_version
from (
<select sdk from ...>
) my_data

Answer 2

你可以为此使用翻译：

SELECT sdk, translate(sdk,'0123456789/.','') AS sdk_name, 
translate(lower(sdk),'abcdefghijklmnopqrstuvwxyz/','') AS sdk_version 
FROM   table1;

工作小提琴

编辑(由戈登(：

这是个好主意。 我发现使用regexp_replace()更简单：

select regexp_replace(sdk, '[0-9/.]', '', 'g') as sdk_name,
regexp_replace(sdk, '[a-zA-Z/]', '', 'g') as sdk_version

Answer 3

正则表达式解析是计算密集型的，因此与其使用两个函数调用(如其他答案(，不如将它们组合成一个调用，然后从结果中提取所需的值：

WITH d(sdk) AS (
VALUES
('JavaScript/2.3.4'),
('JavaScript/4.3.1'),
('Android4.6.5'),
('Android3.2.1'),
('Swift4.5.3'),
('Swift/3.1.1.5'),
('C#/23.1') )
SELECT unq.sdk, re.match[1] AS sdk_name, re.match[2] AS sdk_version
FROM (SELECT DISTINCT sdk FROM d) unq,
regexp_match(unq.sdk, '([^0-9/]*)/*([0-9.]*)') re (match);

正则表达式执行以下操作：

1. ([^0-9/]*)捕获第一个数字或正斜杠之前的所有内容。请注意，这也将匹配具有A-Za-z以外的字符的 SDK 名称。
2. /*跳过正斜杠(如果存在(
3. ([0-9.]*)捕获任何以下数字或点。如果您确信只有数字和点会跟随，那么您也可以做(*)。

另请注意，我将DISTINCT子句放在单独的子查询中。首先处理每一行然后抛出任何重复项不是很有效。相反，请先删除重复项。

PG-10 之前的版本

函数regexp_match()在版本 10 中引入。如果您有旧版本，则可以使用不带g标志的regexp_matches()来获得相同的结果 (PG8.3+(。