34,838
社区成员




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 函数的长度参数无效。
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 行
传递给 LEFT 或 SUBSTRING 函数的长度参数无效。
*/