SQL 自定SPLIT函数的实际运用

--当表中FarmAgriculturalName字段中包含'、'字符时，返回以字符串分割后的表数据数量（取分割后的临时表中长度  > 1 的数据），条件为FarmAgriculturalName的长度>1

原始表结构：

select (case when CHARINDEX('、', FarmAgriculturalName) > 0 then 
(select count(1) from dbo.ufn_SplitToTable(FarmAgriculturalName, '、') 
where LEN(word) > 1) else 1 end ) as datacount
from DataInput_AARD where LEN(FarmAgriculturalName) > 1  

该步结果：

创建农业企业为name字段，对上面SQL求出的结果求和

select '农业企业' as name,
(select sum(a.datacount) as NYHZSCount from (select (case when CHARINDEX('、', FarmAgriculturalName) > 0 then 
(select count(1) from dbo.ufn_SplitToTable(FarmAgriculturalName, '、') 
where LEN(word) > 1) else 1 end ) as datacount
from DataInput_AARD where LEN(FarmAgriculturalName) > 1  ) as a) as [value]

最终结果：