两个字符之间的变长子串

问题描述

数据如下所示:

Initiative: Credible Sources;
Initiative: Just in Time;
Initiative: Database Normalization;

我希望它看起来像这样:

I want it to look like this:

Credible Sources
Just in Time
Database Normalization

摆脱其中一个非常简单.

It's pretty simple to get rid of one or the other.

这个:

SELECT DISTINCT LEFT(OPTIONAL_FIELD_2, CHARINDEX(';', OPTIONAL_FIELD_2 + ';')-1) AS OPTIONAL_FIELD_2
FROM my_table
ORDER BY OPTIONAL_FIELD_2

给我这个:倡议:可靠来源倡议:及时倡议:数据库规范化

Gives me this: Initiative: Credible Sources Initiative: Just in Time Initiative: Database Normalization

还有这个:

SELECT DISTINCT RIGHT(OPTIONAL_FIELD_2, LEN(OPTIONAL_FIELD_2)-12) AS OPTIONAL_FIELD_2
FROM my_table
ORDER BY OPTIONAL_FIELD_2

给我这个:

Credible Sources;
Just in Time;
Database Normalization;

很难弄清楚如何将两者结合起来.

Having a hard time figuring out how to combine the two.

推荐答案

仅使用 substring() 怎么样?

select replace(substring(option_field_2, 13, 999), ';', '')

或者，如果您不知道前缀有多长:

Or, if you don't know how long the prefix is:

select replace(stuff(option_field_2, 1, charindex(':', option_field_2) + 1, ''), ';', '')

这里是db<>fiddle.

Here is a db<>fiddle.