字段 A 字段B 字段C
L|P|K|2J 3|3|3|0 3
2J|L|P|K|F 5|5|5|5 5
F|2J|L 12|0|0 12
L|F|K 0|0|0 7
字段A用 "|" 分割 得到的数据和 字段B分割得到的数据对应
如第一行
字段Ａ得到 "L","P","K","2J" 字段Ｂ分割得到 "3","3","3","0"
L对应的是3
我现在想要查询到的结果是
字段A like '%L%' and 字段B分割后的对应L的数字=字段C
另外还要查询
字段A like '%2J%' and 字段B分割后的对应2J的数字=字段C
请大家指点一下谢谢
------解决方案--------------------
- SQL code
create table imx(colA varchar(10), colB varchar(10), colC varchar(10))insert into imxselect 'L|P|K|2J', '3|3|3|0', '3' union allselect '2J|L|P|K|F', '5|5|5|5', '5' union allselect 'F|2J|L', '12|0|0', '12' union allselect 'L|F|K', '0|0|0', '7'create function dbo.fun_imx(@colA varchar(10), @colB varchar(10), @colC varchar(10), @x varchar(10)) returns char(1)begin declare @tca table(id int identity(1,1),colA varchar(10)) declare @tcb table(id int identity(1,1),colB varchar(10)) insert into @tca(colA) select substring(a.col,b.number, charindex('|',a.col+'|',b.number)-b.number) col from (select @colA col) a inner join master.dbo.spt_values b on b.type='P' and substring('|'+a.col,b.number,1)='|' insert into @tcb(colB) select substring(a.col,b.number, charindex('|',a.col+'|',b.number)-b.number) col from (select @colB col) a inner join master.dbo.spt_values b on b.type='P' and substring('|'+a.col,b.number,1)='|' declare @r char(1) if (exists(select 1 from @tca where [email protected]) and exists(select 1 from @tcb b,@tca a where b.id=a.id and [email protected]) and (select b.colB from @tcb b,@tca a where b.id=a.id and [email protected])[email protected]) select @r='Y' else select @r='N' return @renddeclare @x varchar(10)select @x='L'select * from imxwhere dbo.fun_imx(colA,colB,colC,@x)='Y'colA colB colC---------- ---------- ----------L|P|K|2J 3|3|3|0 32J|L|P|K|F 5|5|5|5 5(2 row(s) affected)