DECLARE @TB TABLE(id INT, [str] VARCHAR(10))
INSERT @TB
SELECT 1, '3,5,7,12' UNION ALL
SELECT 2, '5,23,54,88' UNION ALL
SELECT 3, '2,3,9'SELECT B.ID,COUNT(*)
FROM @TB B ,(SELECT ID=(SELECT COUNT(*) FROM SYSOBJECTS WHERE ID<O.ID) FROM SYSOBJECTS O) T
WHERE CHARINDEX(',', [STR]+',', T.ID)=T.ID
GROUP BY B.ID
ORDER BY B.ID
/*
ID
----------- -----------
1 4
2 4
3 3
*/
INSERT @TB
SELECT 1, '3,5,7,12' UNION ALL
SELECT 2, '5,23,54,88' UNION ALL
SELECT 3, '2,3,9'SELECT B.ID,COUNT(*)
FROM @TB B ,(SELECT ID=(SELECT COUNT(*) FROM SYSOBJECTS WHERE ID<O.ID) FROM SYSOBJECTS O) T
WHERE CHARINDEX(',', [STR]+',', T.ID)=T.ID
GROUP BY B.ID
ORDER BY B.ID
/*
ID
----------- -----------
1 4
2 4
3 3
*/
*/
create table #t (id int, [str] varchar(20))
insert into #t
select 1,'3,5,7,12' union
select 2,'5,23,54,88' union
select 3,'2,3,9'SELECT A.id, COUNT(B.value )numberSum
FROM(
SELECT id, [str] = CONVERT(xml,' <root> <v>' + REPLACE([str], ',', ' </v> <v>') + ' </v> </root>') FROM #t
)A
OUTER APPLY(
SELECT value = N.v.value('.', 'varchar(100)') FROM A.[str].nodes('/root/v') N(v)
)B
group by A.id
/*
id numberSum
1 4
2 4
3 3
*/
至于那个'爱'字嘛, 随便什么汉字都行, 目的就是要双字节的字符就行
很巧妙,我回家好好试试
create table #t (id int, [str] varchar(20))
insert into #t
select 1,'3,5,7,12' union
select 2,'5,23,54,88' union
select 3,'2,3,9'select id,( datalength(replace([str], ',', ' ')) - len([str]) +1 ) as numberSum
from #t
/*
id numberSum
1 4
2 4
3 3
*/
--计算方法不错
看来要深入判断了, 有以下几种特例目前, 如果空 指的是NULL 则结果是NULL, 如果想要结果为0, 看下面修改
目前,如果空 指的是'' 则结果是不对的, 如果想要结果为0, 看下面修改
目前,还有一种例外, 如果值类似 [3,]或[,3] 也就是逗(,)号之前或之后没有数字, 那算几个呢?
如果算两个就不是特例, 如果算一个, 看下面写个更全面的如下select ID, ( case when isnull(str, '') then 0 when str like '%,' or str like ',%' then 1 else datalength(replace(str, ',', '爱')) - len(str) + 1 end) as numberSum