I have strings like 'keepme:cutme' or 'string-without-separator' which should become respectively 'keepme' and 'string-without-separator'. Can this be done in PostgreSQL? I tried:
我有像'keepme:cutme'或'string-without-separator'这样的字符串,它们应分别成为'keepme'和'string-without-separator'。这可以在PostgreSQL中完成吗?我试过了:
select substring('first:last' from '.+:')
But this leaves the : in and won't work if there is no : in the string.
但是如果在字符串中没有:in,则离开:in并且将不起作用。
3 个解决方案
#1
17
Use split_part():
SELECT split_part('first:last', ':', 1) AS first_part
Returns the whole string if the delimiter is not there. And it's simple to get the 2nd or 3rd part etc.
如果分隔符不存在,则返回整个字符串。获得第2或第3部分等很简单
Substantially faster than functions using regular expression matching. And since we have a fixed delimiter we don't need the magic of regular expressions.
比使用正则表达式匹配的函数快得多。由于我们有一个固定的分隔符,我们不需要正则表达式的魔力。
Related:
- Split comma separated column data into additional columns
将逗号分隔的列数据拆分为其他列
#2
1
regexp_replace() may be overload for what you need, but it also gives the additional benefit of regex. For instance, if strings use multiple delimiters.
regexp_replace()可能会超出您的需要,但它也提供了正则表达式的额外好处。例如,如果字符串使用多个分隔符。
Example use:
select regexp_replace( 'first:last', E':.*', '');
#3
0
SQL Select to pick everything after the last occurrence of a character
SQL选择以在最后一次出现字符后选择所有内容
select right('first:last', charindex(':', reverse('first:last')) - 1)
#1
17
Use split_part():
SELECT split_part('first:last', ':', 1) AS first_part
Returns the whole string if the delimiter is not there. And it's simple to get the 2nd or 3rd part etc.
如果分隔符不存在,则返回整个字符串。获得第2或第3部分等很简单
Substantially faster than functions using regular expression matching. And since we have a fixed delimiter we don't need the magic of regular expressions.
比使用正则表达式匹配的函数快得多。由于我们有一个固定的分隔符,我们不需要正则表达式的魔力。
Related:
- Split comma separated column data into additional columns
将逗号分隔的列数据拆分为其他列
#2
1
regexp_replace() may be overload for what you need, but it also gives the additional benefit of regex. For instance, if strings use multiple delimiters.
regexp_replace()可能会超出您的需要,但它也提供了正则表达式的额外好处。例如,如果字符串使用多个分隔符。
Example use:
select regexp_replace( 'first:last', E':.*', '');
#3
0
SQL Select to pick everything after the last occurrence of a character
SQL选择以在最后一次出现字符后选择所有内容
select right('first:last', charindex(':', reverse('first:last')) - 1)