哪位大神给个字符串处理的解决方案

[骑着蜗牛去爬山]

with tb1(id,name)as(
select 1,'张三1' union all
select 2,'张三2' union all
select 3,'张三3' union all
select 4,'张三4' union all
select 5,'张三5' union all
select 6,'张三6' ),
cte(id,code) as(
select '1,2,3','aa,cc,dd' union all
select '4,5,6','ee,hh,ss'
)

已知字符串"hh"（通过"hh" 查到5， 通过"dd"查到3）
查询出id为5的那条数据，即
5,'张三5'

[二月十六]

分隔函数：

ALTER FUNCTION dbo.f_splitSTR(
@s   varchar(8000),   --待分拆的字符串
@split varchar(10)     --数据分隔符
)RETURNS @re TABLE(col varchar(100))
AS
BEGIN
 DECLARE @splitlen int
 SET @splitlen=LEN(@split+'a')-2
 WHILE CHARINDEX(@split,@s)>0
 BEGIN
  INSERT @re VALUES(LEFT(@s,CHARINDEX(@split,@s)-1))
  SET @s=STUFF(@s,1,CHARINDEX(@split,@s)+@splitlen,'')
 END
 INSERT @re VALUES(@s)
 RETURN
END

GO

--测试数据
if not object_id(N'Tempdb..#tb1') is null
	drop table #tb1
Go
Create table #tb1([id] INT,name NVARCHAR(100))
Insert #tb1
select 1,'张三1' union all
select 2,'张三2' union all
select 3,'张三3' union all
select 4,'张三4' union all
select 5,'张三5' union all
select 6,'张三6'
GO
if not object_id(N'Tempdb..#cte') is null
	drop table #cte
Go
Create table #cte([id] NVARCHAR(100),[code] nvarchar(28))
Insert #cte
select '1,2,3',N'aa,cc,dd' union all
select '4,5,6',N'ee,hh,ss'
Go
--测试数据结束
DECLARE @str NVARCHAR(100)='hh'

SELECT  #tb1.name
FROM    ( SELECT    * ,
                    ROW_NUMBER() OVER ( ORDER BY id ) AS num
          FROM      #cte a
                    CROSS APPLY ( SELECT    col
                                  FROM      dbo.f_splitSTR(a.id, ',')
                                ) t1
        ) tt1
        JOIN ( SELECT   * ,
                        ROW_NUMBER() OVER ( ORDER BY id ) AS num
               FROM     #cte a
                        CROSS APPLY ( SELECT    col
                                      FROM      dbo.f_splitSTR(a.code, ',')
                                    ) t2
             ) tt2 ON tt2.num = tt1.num
        JOIN #tb1 ON #tb1.id = tt1.col
WHERE   tt2.col = @str

[骑着蜗牛去爬山]

看来不用自定义函数不行了，自己写了一个

create function getIdByCode(@ids varchar(200),@codes varchar(200),@code varchar(200)) returns varchar(10)
as
begin
select @ids=','+@ids+',',@codes=','+@codes+',';
declare @id varchar(10)='';
select @id=t1.vl from (select substring(@ids,number+1,charindex(',',@ids,number+1)-number-1) as vl,ROW_NUMBER()over(order by number)as rownum from master..spt_values where type='p' and number between 1 and len(@ids)-1 and substring(@ids,number,1)=',')t1
join(select substring(@codes,number+1,charindex(',',@codes,number+1)-number-1) as vl,ROW_NUMBER()over(order by number)as rownum from master..spt_values where type='p' and number between 1 and len(@codes)-1 and substring(@codes,number,1)=',')t2 on t1.rownum=t2.rownum
where t2.vl=@code
return @id;
end

[二月十六]

引用 2 楼 hjywyj 的回复:

看来不用自定义函数不行了，自己写了一个

create function getIdByCode(@ids varchar(200),@codes varchar(200),@code varchar(200)) returns varchar(10)
as
begin
select @ids=','+@ids+',',@codes=','+@codes+',';
declare @id varchar(10)='';
select @id=t1.vl from (select substring(@ids,number+1,charindex(',',@ids,number+1)-number-1) as vl,ROW_NUMBER()over(order by number)as rownum from master..spt_values where type='p' and number between 1 and len(@ids)-1 and substring(@ids,number,1)=',')t1
join(select substring(@codes,number+1,charindex(',',@codes,number+1)-number-1) as vl,ROW_NUMBER()over(order by number)as rownum from master..spt_values where type='p' and number between 1 and len(@codes)-1 and substring(@codes,number,1)=',')t2 on t1.rownum=t2.rownum
where t2.vl=@code
return @id;
end

自己都写出来，你这是社区送温暖，给我送分呢么？ 第一次得200分的帖子

[骑着蜗牛去爬山]

引用 3 楼 sinat_28984567 的回复:

自己都写出来，你这是社区送温暖，给我送分呢么？ 第一次得200分的帖子

我只是想知道不用自定义函数有没有更简单快速高效的解决方法