split string value in sql server

create FUNCTION [dbo].[Split](@String varchar(8000), @Delimiter char(1))
returns @temptable TABLE (DU varchar(8000))
as
begin
   declare @idx int
    declare @slice varchar(8000)

    select @idx = 1
        if len(@String)<1 or @String is null  return

    while @idx!= 0
    begin
        set @idx = charindex(@Delimiter,@String)
        if @idx!=0
           set @slice = left(@String,@idx - 1)
        else
            set @slice = @String

        if(len(@slice)>0)
            insert into @temptable(DU) values(@slice)

       set @String = right(@String,len(@String) - @idx)
        if len(@String) = 0 break
    end
return
end


--select * from dbo.split('1,2',',')

 declare @tble table 
 ( id int ,
 username nvarchar(50),
 orderid nvarchar(50) )

 declare  @id int , @user nvarchar(50) , @orderid nvarchar(50)
 
DECLARE @cur    CURSOR
SET @cur = CURSOR FAST_FORWARD
 FOR SELECT * FROM tblUserDetail
OPEN @cur
FETCH NEXT
FROM @cur INTO @id,@user,@orderid
WHILE @@FETCH_STATUS = 0
BEGIN

 declare @tmp table ( id int)
 delete @tmp
   insert into @tmp select * from  dbo.split(@orderid,',')
   
    declare @idd int
    declare @cur_inner cursor 
    set @cur_inner = cursor fast_forward
    for select * from @tmp 
    open @cur_inner 
    fetch next from @cur_inner into @idd 
    WHILE @@FETCH_STATUS = 0
BEGIN
 
  insert into @tble (id,orderid ,username) values ( @id, @idd ,@user) 

  
fetch next from @cur_inner into @idd 
END
CLOSE @cur_inner
DEALLOCATE @cur_inner 
 
 
 
FETCH NEXT
FROM @cur INTO @id,@user,@orderid
END
CLOSE @cur
DEALLOCATE @cur

select t.id,t.orderid,t.username,ord.trainno  from @tble t inner join tblorder ord 
on t.orderid = ord.orderid

<pre lang="sql">SELECT
tblOrder.OrderId,
tblUserDetail.UserName,
tblOrder.TrainNo
FROM tblOrder
INNER JOIN tblUserDetail ON
'#' + REPLACE(tblUserDetail.OrderId, ',','#') + '#' like '%#' + convert(nvarchar(100), tblOrder.OrderId) + '#%'