I usually do this with the following code:

create function [dbo].[Split](@string varchar(max), @separator varchar(10))
returns @splited table ( stringPart varchar(max) )
with execute as caller
as
begin
    declare @stringPart varchar(max);
    set @stringPart = '';
    
    while charindex(@separator, @string) > 0
    begin
        set @stringPart = substring(@string, 0, charindex(@separator, @string));
        insert into @splited (stringPart) values (@stringPart);
        set @string = substring(@string, charindex(@separator, @string) + 1, len(@string) + 1);
    end
    
    return;
end
go

You can test it with this query:

declare @example varchar(max);
set @example = 'one;string;to;rule;them;all;;';

select * from [dbo].[Split](@example, ';');

I usually do this with the following code:

create function [dbo].[Split](@string varchar(max), @separator varchar(10))
returns @splited table ( stringPart varchar(max) )
with execute as caller
as
begin
    declare @stringPart varchar(max);
    set @stringPart = '';
    
    while charindex(@separator, @string) > 0
    begin
        set @stringPart = substring(@string, 0, charindex(@separator, @string));
        insert into @splited (stringPart) values (@stringPart);
        set @string = substring(@string, charindex(@separator, @string) + len(@separator), len(@string) + 1);
    end
    
    return;
end
go

You can test it with this query:

declare @example varchar(max);
set @example = 'one;string;to;rule;them;all;;';

select * from [dbo].[Split](@example, ';');