Remove part of a string after a character until a charcter and repeat until the string end

Question

I am trying to filter part of a string until it reaches a specific character and repeat until the end of string.

Data loooks like

095930
CF0010+EN
060983+PS
086588+GG;086326+GG
900010;
CF0002;;CF0018;

Output should be

095930
CF0010
060983
086588;086326
900010;
CF0002;;CF0018;

I tried the following:

SUBSTRING(column, 1, CHARINDEX('+',column+ '+', 1)-1)

But this removes everything after the + which is not what I need.

Answer 1

Removing everything after and including a character in a value is quite simple, just use LEFT and CHARINDEX:

LEFT(DS.Item,CHARINDEX('+',DS.Item + '+')-1)

The real problem you have is a little more complex:

1. You have a denormalised design, making this far more difficult.
2. You want to retain the denormalised design in the results, making this difficult again
3. You are using a version of SQL Server that is very close to end of support that has no in built support for string splitting and aggregation.

Saying that, unless you were using Azure SQL Database (or somehow had a copy of SQL Server 2022) I wouldn't suggest STRING_SPLIT, as it doesn't provide an ordinal position parameter. Though STRING_AGG would make things far easier; and you could use a JSON splitter too.

Instead I use DelimitedSplit8K_LEAD here, and then "Ye Olde FOR XML PATHe (and STUFF)" for the string aggregation. This gives this clunky solution:

SELECT *
INTO dbo.YourTable
FROM (VALUES('095930'),
            ('CF0010+EN'),
            ('060983+PS'),
            ('086588+GG;086326+GG'),
            ('900010;'),
            ('CF0002;;CF0018;'))V(YourString);

GO

SELECT YT.YourString,
       STUFF((SELECT ';' + LEFT(DS.Item,CHARINDEX('+',DS.Item + '+')-1)
              FROM dbo.DelimitedSplit8K_LEAD(YT.YourString,';') DS
              ORDER BY DS.ItemNumber
              FOR XML PATH(''),TYPE).value('(./text())[1]','varchar(8000)'),1,1,'') AS NewString
FROM dbo.YourTable YT; 
     

GO
DROP TABLE dbo.YourTable;

If you were using Azure SQL Database (or SQL Server 2022) then the answer would be much simpler:

SELECT YT.YourString,
       STRING_AGG(LEFT(SS.Value,CHARINDEX('+',SS.Value + '+')-1),';') WITHIN GROUP (ORDER BY SS.Ordinal) AS NewString
FROM dbo.YourTable YT
     CROSS APPLY STRING_SPLIT(YT.YourString,';',1) SS
GROUP BY YT.YourString; --Assuming YourString has a unique value