SQL Server 2014

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table your_table (
 id int identity primary key, 
 fieldname varchar(100)
);
insert into your_table (fieldname) values
('0001, ABCD1234;0002, EFGH432562;0003, IJKL1345hsth;');
1 rows affected

CREATE FUNCTION dbo.fnString_Split
(
    @str    nvarchar(4000), 
    @delim  nchar(1)
)
RETURNS TABLE 
WITH SCHEMABINDING 
AS 
RETURN
(
  WITH RCTE AS (
    SELECT 
      1 AS ordinal
    , ISNULL(NULLIF(CHARINDEX(@delim, @str),0), LEN(@str)) AS pos
    , LEFT(@str, ISNULL(NULLIF(CHARINDEX(@delim, @str),0)-1, LEN(@str))) AS value
    UNION ALL
    SELECT 
      ordinal+1
    , ISNULL(NULLIF(CHARINDEX(@delim, @str, pos+1), 0), LEN(@str))
    , SUBSTRING(@str, pos+1, ISNULL(NULLIF(CHARINDEX(@delim, @str, pos+1),0)-pos-1, LEN(@str)-pos )) 
    FROM RCTE
    WHERE pos < LEN(@str)
  ) 
  SELECT ordinal, value
  FROM RCTE
);
SELECT id, codes
FROM your_table
CROSS APPLY (
  SELECT RTRIM((
       SELECT LEFT(value, PATINDEX('%_,%', value))+' '
       FROM dbo.fnString_Split(fieldname, ';') AS spl
       WHERE value LIKE '%_,%'
       ORDER BY ordinal
       FOR XML PATH(''), TYPE).value(N'./text()[1]', N'nvarchar(max)')
    ) AS codes
) ca
OPTION (MAXRECURSION 250);


id
codes


1
0001 0002 0003