列中有分割符，变多行或者统计数量

[wanliang2001]

号码列中有个分隔符；
1. 我想查询结果为每行记录只有1个号码。这样我可以SUM求和。
2.另一个结果，我想查询每行有多少个号码。

[卖水果的net]

-- 求 分号个数 select len(col) - len(replace(str,';','') +1

[唐诗三百首]

create table #t(InnerID int,SMSPhone varchar(50))

insert into #t
  select 1,'101;102;103' union all
  select 2,'210;220'


-- 1. 我想查询结果为每行记录只有1个号码。这样我可以SUM求和。
select a.InnerID,
           SMSPhone=substring(a.SMSPhone,b.number,charindex(';',a.SMSPhone+';',b.number)-b.number)
from #t a
inner join master.dbo.spt_values b on b.number between 1 and len(a.SMSPhone)
                                                                and substring(';'+a.SMSPhone,b.number,1)=';'
where b.[type]=N'P' 

/*
InnerID     SMSPhone
----------- --------------------------------------------------
1           101
1           102
1           103
2           210
2           220

(5 row(s) affected)
*/


-- 2.另一个结果，我想查询每行有多少个号码。
with t as(
select a.InnerID,
           SMSPhone=substring(a.SMSPhone,b.number,charindex(';',a.SMSPhone+';',b.number)-b.number)
from #t a
inner join master.dbo.spt_values b on b.number between 1 and len(a.SMSPhone)
                                                                and substring(';'+a.SMSPhone,b.number,1)=';'
where b.[type]=N'P' )
select InnerID,qty=count(1) 
 from t 
 group by InnerID

/*
InnerID     qty
----------- -----------
1           3
2           2

(2 row(s) affected)
*/

[道素]

可以先按照下面的逻辑将每个拆分的值转换为行，然后在根据你的需要去统计

;WITH t(ID,s) AS (
	SELECT 1, 'ABC' UNION ALL
	SELECT 2, 'ABC;123' UNION ALL
	SELECT 3,'ABC;ABD;123'
) 
SELECT * FROM (
   SELECT *,CONVERT(XML,'<r><n>'+REPLACE(t.s,';','</n><n>')+'</n></r>') AS x FROM t
)  AS a
CROSS APPLY(SELECT s.b.value('.','varchar(100)') AS sb FROM a.x.nodes('r/n') as s(b)) c

;WITH t(ID,s) AS (
	SELECT 1, 'ABC' UNION ALL
	SELECT 2, 'ABC;123' UNION ALL
	SELECT 3,'ABC;ABD;123'
) 
SELECT a.ID,count(c.sb) FROM (
   SELECT *,CONVERT(XML,'<r><n>'+REPLACE(t.s,';','</n><n>')+'</n></r>') AS x FROM t
)  AS a
CROSS APPLY(SELECT s.b.value('.','varchar(100)') AS sb FROM a.x.nodes('r/n') as s(b)) c
GROUP BY a.ID