Sql 字符串 分割，拼接 问题

[十三-]

这里有一个字符串，包含N组值
现在两组值为例以逗号隔开，
每组值含义(值 | 字段类型 1代表 字符串，2代表数字|字段名称)
字符串 DN50|1|koujing,10|2|yalidengji 
Att 表结构如下
ID     koujing   yalidengji  
1       DN50       10
2       DN100      20
3       DN50       15
4       DN100      30

现在要根据字符串 拼接出如下sql  条件
select * from Att where koujing='DN50' and yalidengji=10

结果如下
ID     koujing   yalidengji  
1       DN50       10

[xqchenxue2]

declare @str varchar(126); set @str='DN50|1|koujing,10|2|yalidengji'; set @str=REPLACE(@str,',' ,' and ') set @str=REPLACE(@str,'|1|' ,''' = '); set @str=REPLACE(@str,'|2|' ,' = '); set @str='select * from Att where '''+@str; print(@str);

[十三-]

引用 1 楼 xqchenxue2 的回复:

declare @str varchar(126); set @str='DN50|1|koujing,10|2|yalidengji'; set @str=REPLACE(@str,',' ,' and ') set @str=REPLACE(@str,'|1|' ,''' = '); set @str=REPLACE(@str,'|2|' ,' = '); set @str='select * from Att where '''+@str; print(@str);

还不太对哦，不能写死的，可能不只两组值，而且里面值是不确定的

[xqchenxue2]

我只是提供一个思路而已

[xqchenxue2]

那也简单啊，你可以 ',|1|' ',|2|' '1' '2' 依次替换,不就OK了么

[xqchenxue2]

说错了,楼上那条

[卖水果的net]

create table test (id int , koujing varchar(10) , yalidengji int)
go
insert into test (id , koujing,yalidengji) values
(1,'DN50' ,10),
(2,'DN100',20),
(3,'DN50' ,15),
(4,'DN100',30)
go
declare @condition varchar(100) = 'DN50|1|koujing,10|2|yalidengji'
declare @sql varchar(200) = 'select * from test where 1 = 1 '
declare @splitcount int , @loop int = 1
set @condition = ' ,' + @condition 
set @splitcount = len(@condition) - len(replace(@condition,',',''))
while @loop <= @splitcount 
    begin
        set @condition = replace(@condition,',',' and ''')
        set @condition = REPLACE(@condition,'|' + cast(@loop as varchar(3)) + '|',''' = ')
        set @loop = @loop + 1
    end
set @sql = @sql + @condition
print @sql 
exec (@sql )
go
drop table test 
go


(4 行受影响)
select * from test where 1 = 1   and 'DN50' = koujing and '10' = yalidengji
id          koujing    yalidengji
----------- ---------- -----------
1           DN50       10

(1 行受影响)

[spiritofdragon]

declare @s varchar(1000)='DN50|1|koujing,10|2|yalidengji,2000|2|xxxxxxxx,fsasadfasdf|1|yyyyyy';
declare @where varchar(2000)='';
with t as (select @s+',' s)
,cte as (
	select *
		,1 lvl
		,substring(s,1,CHARINDEX(',',s)-1) one
		,substring(s,CHARINDEX(',',s)+1,4000) rest
	from t
	union all
	select t.*
		,cte.lvl+1 
		,substring(cte.rest,1,CHARINDEX(',',cte.rest)-1)
		,substring(cte.rest,CHARINDEX(',',cte.rest)+1,4000)
	from t cross join cte 
	where CHARINDEX(',',cte.rest)>0
)
,tt as (select cte.s,lvl,cte.one,cte.one+'|' ss from cte)
,cte2 as (
	select *
		,1 lvl2
		,substring(ss,1,CHARINDEX('|',ss)-1) one2
		,substring(ss,CHARINDEX('|',ss)+1,4000) rest2
	from tt
	union all
	select tt.*
		,cte2.lvl2+1
		,substring(cte2.rest2,1,CHARINDEX('|',cte2.rest2)-1)
		,substring(cte2.rest2,CHARINDEX('|',cte2.rest2)+1,4000)
	from tt join cte2 on tt.one=cte2.one
	where CHARINDEX('|',cte2.rest2)>0
)
,ttt (id,col,type,val)as
(
select 
	cte2.lvl
	,max(case lvl2 when 3 then one2 end)
	,max(case lvl2 when 2 then one2 end)
	,max(case lvl2 when 1 then one2 end)
from cte2
group by cte2.lvl
)
select @where=@where+' and '+ttt.col+'='+case when ttt.type=1 then '''' else '' end+''+ttt.val+''+case when ttt.type=1 then '''' else '' end+''
from ttt
order by id;
if len(@where)>0
	set @where=' where'+SUBSTRING(@where,5,4000)
print @where;