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.
Advertisement
Answer
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.