--参考邹老大写的函数/* 建立日期:2007-01-03 参考网址: 功能描述: 循环截取法 */ --创建函数 IF(OBJECTPROPERTY(OBJECT_ID('FUN_splitSTR'), 'IsTableFunction') IS NOT NULL) DROP FUNCTION FUN_splitSTR GO CREATE FUNCTION dbo.FUN_splitSTR( @str varchar(8000), --待拆分的字符串 @split varchar(10) --分隔符 ) RETURNS @re TABLE(col varchar(100)) AS BEGIN DECLARE @splieLen int SET @splieLen=LEN(@split+'a')-2 --为了防止数据分隔符为空格,或者数据分隔符尾部包含空格时,导致数据分隔符长度计算错误 WHILE CHARINDEX(@split, @str)>0 BEGIN INSERT @re VALUES(LEFT(@str, CHARINDEX(@split, @str)-1)) SET @str=STUFF(@str, 1, CHARINDEX(@split, @str)+@splieLen, '') END INSERT @re VALUES(@str) --将最后一个数据项保存到结果表中 RETURN END GO --测试函数 DECLARE @str varchar(200) SET @str='1,23,a' SELECT * FROM dbo.FUN_splitSTR(@str, ',')
select * from (select count(zj) as '高血压' from tableName where zj like '%高血压%') a, (select count(zj) as '冠心病' from tableName where zj like '%冠心病%') b, (select count(zj) as '白内障' from tableName where zj like '%白内障%') c, (select count(zj) as '前列腺炎' from tableName where zj like '%前列腺炎%') d
建立日期:2007-01-03
参考网址:
功能描述:
循环截取法
*/
--创建函数
IF(OBJECTPROPERTY(OBJECT_ID('FUN_splitSTR'), 'IsTableFunction') IS NOT NULL)
DROP FUNCTION FUN_splitSTR
GO
CREATE FUNCTION dbo.FUN_splitSTR(
@str varchar(8000), --待拆分的字符串
@split varchar(10) --分隔符
)
RETURNS @re TABLE(col varchar(100))
AS
BEGIN
DECLARE @splieLen int
SET @splieLen=LEN(@split+'a')-2 --为了防止数据分隔符为空格,或者数据分隔符尾部包含空格时,导致数据分隔符长度计算错误 WHILE CHARINDEX(@split, @str)>0
BEGIN
INSERT @re VALUES(LEFT(@str, CHARINDEX(@split, @str)-1))
SET @str=STUFF(@str, 1, CHARINDEX(@split, @str)+@splieLen, '')
END
INSERT @re VALUES(@str) --将最后一个数据项保存到结果表中 RETURN
END
GO
--测试函数
DECLARE @str varchar(200)
SET @str='1,23,a'
SELECT * FROM dbo.FUN_splitSTR(@str, ',')
(select count(zj) as '高血压' from tableName where zj like '%高血压%') a,
(select count(zj) as '冠心病' from tableName where zj like '%冠心病%') b,
(select count(zj) as '白内障' from tableName where zj like '%白内障%') c,
(select count(zj) as '前列腺炎' from tableName where zj like '%前列腺炎%') d