拆分字符串（将ID串用名称串显示）

最新推荐文章于 2022-12-02 08:39:24 发布

静_心

最新推荐文章于 2022-12-02 08:39:24 发布

阅读量755

点赞数

分类专栏： sql server 文章标签： manager insert table null function string

本文链接：https://blog.csdn.net/jing_xin/article/details/5133726

版权

sql server 专栏收录该内容

20 篇文章 0 订阅

订阅专栏

select * from uf_StrSplit('|5376|86080|T009|A095002|','|')

CREATE FUNCTION uf_StrSplit
(@origStr varchar(7000), --待拆分的字符串
@markStr varchar(100))   --拆分标记，如','
RETURNS @splittable table
(
str_id   varchar(4000) NOT NULL, --编号ID
string   varchar(2000) NOT NULL --拆分后的字符串
)
AS
BEGIN
declare @strlen int,@postion int,@start int,@sublen int,@TEMPstr varchar(200),@TEMPid int
SELECT @strlen=LEN(@origStr),@start=1,@sublen=0,@postion=1,@TEMPstr='',@TEMPid=0

if(RIGHT(@origStr,1)<>@markStr )
begin
set @origStr = @origStr + @markStr
end
WHILE((@postion<=@strlen) and (@postion !=0))
BEGIN
IF(CHARINDEX(@markStr,@origStr,@postion)!=0)
BEGIN
   SET @sublen=CHARINDEX(@markStr,@origStr,@postion)-@postion;
END
ELSE
BEGIN
   SET @sublen=@strlen-@postion+1;

END
IF(@postion<=@strlen)
BEGIN
   SET @TEMPid=@TEMPid+1;
   SET @TEMPstr=SUBSTRING(@origStr,@postion,@sublen);
   INSERT INTO @splittable(str_id,string) values(@TEMPid,@TEMPstr)
   IF(CHARINDEX(@markStr,@origStr,@postion)!=0)
   BEGIN
    SET @postion=CHARINDEX(@markStr,@origStr,@postion)+1
   END
   ELSE
   BEGIN
    SET @postion=@postion+1
   END
END
END
RETURN
END

--drop table #dd
declare @manager varchar(10),@codeid varchar(10),@codestr varchar(200),@managername varchar(30)

create table #dd(manager char(10),managername varchar(30),strid int,auditor varchar(10))

declare cur_xs_temp cursor for
select project_manager,code_id,b.employee_name from projects a,employee b
where g_id=24 and a.project_manager=b.employee_id and b.islizhi<>1
select @manager='',@codestr='',@managername=''
open cur_xs_temp
fetch cur_xs_temp into @manager,@codestr,@managername
while(@@fetch_status=0)
begin
insert into #dd(manager,managername,strid,auditor)
     select @manager as manager,@managername,* from uf_StrSplit(@codestr,'|')
  select @manager='',@codestr='',@managername=''
  fetch cur_xs_temp into @manager,@codestr,@managername
end
close cur_xs_temp
deallocate cur_xs_temp

select a.*,b.employee_name
from #dd a,employee b
where a.auditor=b.employee_id and a.manager<>a.auditor
order by a.manager,a.auditor

通过上面的方法可以实现将一个记录里的“id1|id2|id3”导出成表"name1""name2""name3"