【100分秒结】cte 里面的一个异常帮修正_Sql Server

【100分秒结】cte 里面的一个错误帮修正
刚刚在看帖子帮别人解决问题的时候，发现这么一个奇怪的错误，具体看代码最后的那一条语句！

if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([code] varchar(50),[code2] varchar(50))
insert [tb]
select 'a1,a2','a1a1a1a1,a2a2a2a2' union all
select 'b1,b2','b1b1b1b1,b2b2b2b2'

go

with cte as
(
select [code],
       SUBSTRING(t.[code2], number ,CHARINDEX(',',t.[code2]+',',number)-number) as code2

from [tb] t,master..spt_values s
where s.number >=1
and s.type = 'P'
and SUBSTRING(','+t.[code2],s.number,1) = ','
)
,cte1 as
(
select code2,
       SUBSTRING(t.[code], number ,CHARINDEX(',',t.[code]+',',number)-number) as code  
from cte t,master..spt_values s
where s.number >=1
and s.type = 'P'
and SUBSTRING(','+t.[code],s.number,1) = ',' 
) 
select code ,code2, charindex(code,code2)from cte1   

--select code ,code2 from cte1 where charindex(code,code2)>0

---最后查询的时候   上面一条语句执行没有问题，where 条件加在后面时， 出现这个莫名其妙的错误
-- 下面的会报错 消息 537，级别 16，状态 2，第 2 行
--传递给 LEFT 或 SUBSTRING 函数的长度参数无效。

------解决思路----------------------
我也有啊。这个帖子我也试过了。一样的。这个例子需要对2列分别进行拆分。
必须要分别拆分。然后进行连接。不能一起。
我当时代码这样
select code2,
       SUBSTRING(t.[code], number ,CHARINDEX(',',t.[code]+',',number)-number) as code
from cte t,master..spt_values s
where s.number >=1
and s.type = 'P'
and SUBSTRING(','+t.[code],s.number,1) = ','   or SUBSTRING(','+t.[code2],s.number,1) = ','
一样报这个错误。因为对于CODE2来说他的CHARINDEX(',',t.[code2]+',',number)=10，
用来来截断CODE 就会报错啊LEN（code)<10
------解决思路----------------------
你把你的CTE1里面的SELECT 换成*  看看CTE1 你就懂了。为啥这个不能用上面的CHARINDEX。
------解决思路----------------------
反复测了一下，并且google了一下，只能说这是编译的过程中顺序并不是你写法那样，即使我再用一个cte包住同样出错，最后如果我把select code ,code2, charindex(code,code2)  as c into #t from cte1
然后再
select * from #t where c>0就不抱错了，因为不需要重新计算charindex(code,code2)
------解决思路----------------------
另外只有charindex(code,code2)=0才报错，=1不抱错
------解决思路----------------------

引用:

Quote: 引用:

你把你的CTE1里面的SELECT 换成* 看看CTE1 你就懂了。为啥这个不能用上面的CHARINDEX。

感谢你的回复，不过你解释了一堆貌似没什么用。换成*查询又如何，后面加条件还是会...

不好意思我看错了。你这个报错和我的一样。但是写法不一样。
你先把最后的CTE1结果插入到一个表里面。然后再从这个里面进行SELECT就不会报错。
所以我觉得原因应该是CTE不会提高性能。只能方便阅读。
所以 SUBSTRING(','+t.[code],s.number,1) = ',' 这个条件和CHARINDEX(code,code2)>0
没有先后问题。
------解决思路----------------------
但是这个报错内容还是让我不理解。看这个报错。应该是SUBSTRING发生溢出啊。为啥不加charindex
不报错。加了就溢出。
------解决思路----------------------
最近看《防御式编程》的书，里面很多地方说道不要“以为where条件会按照你的写法的顺序来运行”（大概这个意思），如果你真的要按顺序，常规做法是用case when来规定顺序，某些特殊情况下可以借助索引来把处理的优先级提高
------解决思路----------------------
研究了下，好奇葩，查了一些资料，都没有结果，关注一下

------解决思路----------------------

引用:

Quote: 引用:

反复测了一下，并且google了一下，只能说这是编译的过程中顺序并不是你写法那样，即使我再用一个cte包住同样出错，最后如果我把select code ,code2, charindex(code,code2) as c into #t from cte1
然后再
select * from #t where c>0就不抱错了，因为不需要重新计算charindex(code,code2)

版主说的有点道理，cte里面的执行顺序真是令人费解，我在看看其他的人的意见，再结贴！

这个问题，上次在回一个帖子时也有这个问题，只是这个拆分字符串的语句，不是写在cte中的，而是单个语句，就会报错。

这个语句没有问题：

select 
       v,
       SUBSTRING(t.v, number ,CHARINDEX(',',t.v+',',number)-number)
from 
(
select   '1,2,3,4,4.5,6' v 
union all
select '1.5,2,3,4,4.5,6.6'
)t ,master..spt_values s
where s.number >=1
and s.type = 'P'
and SUBSTRING(','+t.v,s.number,1) = ','

然后现在需要把字符串中的小数过滤掉，就有问题了，会报错：


select  *
from 
( 
	select 
		   v,
		   SUBSTRING(t.v, number ,CHARINDEX(',',t.v+',',number)-number) as vv
	from 
	(
	select   '1,2,3,4,4.5,6' v 
	union all
	select '1.5,2,3,4,4.5,6.6'
	)t ,master..spt_values s
	where s.number >=1
	and s.type = 'P'
	and SUBSTRING(','+t.v,s.number,1) = ','
)t
where vv not like '%.%'
/*

消息 537，级别 16，状态 2，第 3 行

【100分秒结】cte 里面 的一个异常帮修正

【100分秒结】cte 里面的一个异常帮修正