create table Tra_illegal 
(illegalid int, thekey varchar(15))

create table Tra_law
(sysid int, thetext varchar(20))

insert into Tra_illegal
select 1011, '1097|10151' union all 
select 1012, '1090|10951'

insert into Tra_law
select 1097, '非法安装报警器' union all
select 10151, '酒后驾驶' union all  
select 1090, '《道法》' union all   
select 10951, '超载'

-- 楼主的函数
create function [f_split](@c varchar(2000),@split varchar(2))
returns @t table(col varchar(20))   
as   
  begin   
    
  while(charindex(@split,@c)<>0)   
  begin   
  insert @t(col) values (substring(@c,1,charindex(@split,@c)-1))   
  set @c = stuff(@c,1,charindex(@split,@c),'')   
  end   
  insert @t(col) values (@c)   
  return   
  end   

-- 用cross apply
select a.*,b.*,c.*
from Tra_illegal a
cross apply [f_split](a.thekey,'|') b
inner join Tra_law c
on b.col=c.sysid

-- 结果
/*
illegalid   thekey          col                  sysid       thetext
----------- --------------- -------------------- ----------- --------------------
1011        1097|10151      1097                 1097        非法安装报警器
1011        1097|10151      10151                10151       酒后驾驶
1012        1090|10951      1090                 1090        《道法》
1012        1090|10951      10951                10951       超载

(4 row(s) affected)
*/