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的字符。
解决方法:取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