传递给 LEFT 或 SUBSTRING 函数的长度参数无效

LEFT(TNAME,CHARINDEX(’.’,TNAME)-1) 取.前面的值
SUBSTRING(TNAME,1,CHARINDEX(’.’,TNAME) - 1)取.前面的值
reverse倒置函数,取最后一个/后的值

问题:传递给 LEFT 或 SUBSTRING 函数的长度参数无效。

   SELECT   SUBSTRING(TNAME,1,CHARINDEX('.',TNAME) - 1) AS NAME,count(1) AS NUM 
  FROM (
   SELECT reverse(SUBSTRING(reverse(TNAME),1,CHARINDEX('/',reverse(TNAME)) - 1)) AS TNAME
     from finelogdb.dbo.FINE_RECORD_EXECUTE 
    WHERE tname LIKE '%人力资源%'
  ) T
   GROUP BY TNAME
 ORDER BY COUNT(1) DESC
 --SQL 错误 [537] [S0002]: 传递给 LEFT 或 SUBSTRING 函数的长度参数无效。
 ------------------------------------
    SELECT DISTINCT  SUBSTRING(TNAME,1,CHARINDEX('.',TNAME) - 1) AS NAME,count(1) AS NUM 
  FROM (
   SELECT reverse(SUBSTRING(reverse(TNAME),1,CHARINDEX('/',reverse(TNAME)) - 1)) AS TNAME
     from finelogdb.dbo.FINE_RECORD_EXECUTE 
    WHERE tname LIKE '%人力资源%'
  ) T
   GROUP BY TNAME
 ORDER BY COUNT(1) DESC
 --SQL 错误 [537] [S0002]: 传递给 LEFT 或 SUBSTRING 函数的长度参数无效。
 ------------------------------------

原因:存在CHARINDEX(’.’,TNAME) - 1)后小于0的字符。
传递给 LEFT 或 SUBSTRING 函数的长度参数无效
解决方法:取top值或者case when 取CHARINDEX(’.’,TNAME) - 1>0的。

 ------------------------------------
    SELECT TOP 10  SUBSTRING(TNAME,1,CHARINDEX('.',TNAME) - 1) AS NAME,count(1) AS NUM 
  FROM (
   SELECT reverse(SUBSTRING(reverse(TNAME),1,CHARINDEX('/',reverse(TNAME)) - 1)) AS TNAME
     from finelogdb.dbo.FINE_RECORD_EXECUTE 
    WHERE tname LIKE '%人力资源%'
  ) T
   GROUP BY TNAME
 ORDER BY COUNT(1) DESC
 ------------------------------------
     SELECT DISTINCT  SUBSTRING(TNAME,1,CASE WHEN CHARINDEX('.',TNAME) - 1>0  THEN CHARINDEX('.',TNAME) - 1 END) AS NAME
  FROM (
   SELECT reverse(SUBSTRING(reverse(TNAME),1,CASE WHEN CHARINDEX('/',reverse(TNAME)) - 1>0 
   then  CHARINDEX('/',reverse(TNAME)) - 1  END ))  AS TNAME
      from finelogdb.dbo.FINE_RECORD_EXECUTE 
    WHERE tname LIKE '%人力资源%'
  ) T
   GROUP BY TNAME
上一篇:ADworld reverse wp - android-app-100


下一篇:6-1 单链表逆转