使用split_part后将值替换为空字段 [英] Substituting value in empty field after using split_part

问题描述

我有两列， id整数和版本文本。我试图将 version 中的字符串转换为整数，以便可以选择id的最大（最新）版本。

I have two columns, id integer and version text. I am trying to convert the strings in version into integers so that I may select the maximum (most recent) version of the id.

但是， id 的第一个实例将自身存储为版本。示例：

However, the the first instance of the id stores itself as the version. Example:

id | version
---+--------
10 | '10'

而不是：

id | version
---+--------
10 | '10-0'

其他行遵循惯例ID：10，版本：10-1。等等。

Additional rows follow the convention id: 10, version: 10-1. Etc.

我该怎么做？我试过 split_part（）并投射为 int 。但是， split_part（version，-，2）将返回看起来像空字符串的内容。我尝试使用 COALESCE（splitpart ...，'0'）来运行此程序，但无济于事，因为它试图读取由字段索引2返回的空字段。 / p>

How can I accomplish this? I have tried split_part() and cast as int. However, split_part(version, "-", 2) will return what looks like an empty string. I have tried running this using a COALESCE(splitpart..., '0') to no avail as it tried to read the empty field returned by the field index 2.

推荐答案

使用 coalesce（）和nullif（），示例：

with my_table(version) as (
values
    ('10'), ('10-1'), ('10-2')
)

select 
    version, 
    split_part(version, '-', 1)::int as major, 
    coalesce(nullif(split_part(version, '-', 2), ''), '0')::int as minor
from my_table

 version | major | minor 
---------+-------+-------
 10      |    10 |     0
 10-1    |    10 |     1
 10-2    |    10 |     2
(3 rows)    

这篇关于使用split_part后将值替换为空字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！