Data looks like this:
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.
This:
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
And this:
SELECT DISTINCT RIGHT(OPTIONAL_FIELD_2, LEN(OPTIONAL_FIELD_2)-12) AS OPTIONAL_FIELD_2
FROM my_table
ORDER BY OPTIONAL_FIELD_2
Gives me this:
Credible Sources;
Just in Time;
Database Normalization;
Having a hard time figuring out how to combine the two.
How about just using 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, ''), ';', '')
Here is a db<>fiddle.
この記事はインターネットから収集されたものであり、転載の際にはソースを示してください。
侵害の場合は、連絡してください[email protected]
コメントを追加