传递到 SUBSTRING 函数的长度参数无效问题
我的代码如下:
SELECT * FROM
( SELECT ti.FNumber,ti.FName, ti.F_111,ti.f_112,CHARINDEX('\',ti.f_111,1) AS len1, PATINDEX(ti.f_111,'\')AS len2,LEN(ti.F_111) AS len3,LEN(REPLACE(ti.f_111,'\','')) len4,
LEN(ti.F_111)-LEN(REPLACE(ti.f_111,'\','')) len5,
CHARINDEX('\',ti.f_111,CHARINDEX('\',ti.f_111,1)+1) len6,
LEFT(f_111,CHARINDEX('\',ti.f_111,1)-1) AS fnumbero, --第一模具代码
SUBSTRING(ti.F_111,CHARINDEX('\',ti.f_111,1)+1,CHARINDEX('\',ti.f_111,CHARINDEX('\',ti.f_111,1)+1)-(CHARINDEX('\',ti.f_111,1)+1)) AS fnumbert,--第二模具代码
SUBSTRING(ti.F_111,CHARINDEX('\',ti.f_111,CHARINDEX('\',ti.f_111,1)+1)+1,100) AS fnumberth,--第三模具代码
LEFT(f_112,CHARINDEX('\',ti.f_112,1)-1) AS fnameo,--第一模具名0称
SUBSTRING(ti.F_112,CHARINDEX('\', RTRIM (LTRIM(ti.f_112)),1)+1,CHARINDEX('\',RTRIM (LTRIM(ti.f_112)),CHARINDEX('\',RTRIM (LTRIM(ti.f_112)),1)+1)-(CHARINDEX('\',RTRIM (LTRIM(ti.f_112)),1)+1)) AS fnamet,--第二模具名称
SUBSTRING(ti.F_112,CHARINDEX('\',ti.f_112,CHARINDEX('\',RTRIM (ti.f_112),1)+1)+1,100) AS fnameth --第三模具名称
FROM t_ICItem ti
WHERE ti.FNumber LIKE '2%' AND LEN(ti.F_111)-LEN(REPLACE(f_111,'\',''))=2
UNION ALL
SELECT ti.FNumber,ti.FName, ti.F_111,ti.f_112,CHARINDEX('\',ti.f_111,1) AS len1, PATINDEX(ti.f_111,'\')AS len2,LEN(ti.F_111) AS len3,LEN(REPLACE(ti.f_111,'\','')) len4,
LEN(ti.F_111)-LEN(REPLACE(ti.f_111,'\','')) len5,
CHARINDEX('\',ti.f_111,CHARINDEX('\',ti.f_111,1)+1) len6,
LEFT(f_111,CHARINDEX('\',ti.f_111,1)-1) AS fnumbero, --第一模具代码
SUBSTRING(ti.F_111,CHARINDEX('\',ti.f_111,CHARINDEX('\',ti.f_111,1))+1,100) AS fnumbert, '',--第三模具代码
LEFT(f_112,CHARINDEX('\',ti.f_112,1)-1) AS fnameo, --第一模具名称
SUBSTRING(ti.F_112,CHARINDEX('\',ti.f_112,CHARINDEX('\',RTRIM (ti.f_112),1))+1,100) AS fnamet ,'' --第三模具名称
FROM t_ICItem ti
WHERE ti.FNumber LIKE '2%' AND LEN(ti.F_112)-LEN(REPLACE(f_112,'\',''))=1
UNION ALL
SELECT ti.FNumber,ti.FName, ti.F_111,ti.f_112,CHARINDEX('\',ti.f_111,1) AS len1, PATINDEX(ti.f_111,'\')AS len2,LEN(ti.F_111) AS len3,LEN(REPLACE(ti.f_111,'\','')) len4,
LEN(ti.F_111)-LEN(REPLACE(ti.f_111,'\','')) len5,
CHARINDEX('\',ti.f_111,CHARINDEX('\',ti.f_111,1)+1) len6,
TI.F_111 AS FNUMBERO,'','',TI.F_112 AS FNAMEO,'' ,''
FROM t_ICItem ti
WHERE ti.FNumber LIKE '2%' AND LEN(ti.F_112)-LEN(REPLACE(f_112,'\',''))=0
) a
WHERE a.fnamet='' and a.fnameth=''
我想把a.fnamet='' and a.fnameth=''为空是涮选出来， 当我不加 WHERE a.fnamet='' and a.fnameth=''条件是运行正常，加该条件执行时就提示 "传递到 SUBSTRING 函数的长度参数无效。"
请大虾帮忙看看是那里的问题，非常谢谢。
[解决办法]
SUBSTRING(ti.F_111,CHARINDEX('\',ti.f_111,1)+1,CHARINDEX('\',ti.f_111,CHARINDEX('\',ti.f_111,1)+1)-(CHARINDEX('\',ti.f_111,1)+1)) AS fnumbert,--第二模具代码
SUBSTRING(ti.F_111,CHARINDEX('\',ti.f_111,CHARINDEX('\',ti.f_111,1)+1)+1,100) AS fnumberth,--第三模具代码
LEFT(f_112,CHARINDEX('\',ti.f_112,1)-1) AS fnameo,--第一模具名0称
SUBSTRING(ti.F_112,CHARINDEX('\', RTRIM (LTRIM(ti.f_112)),1)+1,CHARINDEX('\',RTRIM (LTRIM(ti.f_112)),CHARINDEX('\',RTRIM (LTRIM(ti.f_112)),1)+1)-(CHARINDEX('\',RTRIM (LTRIM(ti.f_112)),1)+1)) AS fnamet,--第二模具名称
SUBSTRING(ti.F_112,CHARINDEX('\',ti.f_112,CHARINDEX('\',RTRIM (ti.f_112),1)+1)+1,100) AS fnameth --第三模具名称
WHERE a.fnamet='' and a.fnameth=''当加这个条件时，是否有ti.F_111字段和ti.f_112字段为空或者NULL的情况，请注意排查
