22,210
社区成员
发帖
与我相关
我的任务
分享
declare @t table (col ntext)
insert into @t
select '10002727920 10002727926 10002727927 10002727928 ' union all
select '10002727920 10002727926 10002727927 10002727928 ' union all
select '10002727920 10002727926 10002727927 10002727928 ' union all
select '80002727926 80002727927 80002727928 11111111111 22222222222 ' union all
select '80002727920 80002727926 80002727927 80002727928' union all
select '10002727920 10002727926 10002727927 10002727928' union all
select '13609303497' union all
select '18609489876' union all
select '18609489876' union all
select '10002627920' union all
select '10002627920' union all
select '10002627920'
select
sum(ceiling(len(cast(col as varchar(8000)))/12.00)) from @t
/*
31
*/
declare @t table (col VARCHAR(60))
insert into @t
select '14002727924' union all
select '14002727924 14002727926' union all
select '14002727924 14002727926 14002727927' union all
select '84002727926 84002727927 84002727928 84002727928' union all
select '84002727924 84002727926 84002727927 84002727928' union all
select '14002727924 14002727926 14002727927 14002727928 84002727928'
;with cte as (
select B.value as value, rn=ROW_NUMBER()over(partition by value order by getdate())
from(
select
col = convert(xml,' <root> <v>' + replace(col, ' ', ' </v> <v>') + ' </v> </root>')
from @t
)A
outer apply(
select value = N.v.value('.', 'varchar(100)') from A.col.nodes('/root/v') N(v)
)B )
select value,MAX(rn)as 出现的次数 from cte group by value
value 出现的次数
---------------------------------------------------------------------------------------------------- --------------------
14002727924 4
14002727926 3
14002727927 2
14002727928 1
84002727924 1
84002727926 2
84002727927 2
84002727928 4
(8 行受影响)
--换种思路试一试
select sum(ceiling(len(cast([sendtels] as varchar(8000)))/12.00))
from [zw_user_duanxin] where number=1
SELECT sum(len(CAST([SendTels] AS VARCHAR(8000)))-len(replace(CAST([SendTels] AS VARCHAR(8000)),' ',''))+1)
FROM [ZW_User_duanxin]
where number=1
SELECT sum(len(CAST([SendTels] AS VARCHAR(8000))-len(replace(CAST([SendTels] AS VARCHAR(8000),' ',''))+1)
FROM [ZW_User_duanxin]
where number=1
--转换一下类型
declare @t table (col ntext)
insert into @t
select '14002727924' union all
select '14002727924 14002727926' union all
select '14002727924 14002727926 14002727927' union all
select '84002727926 84002727927 84002727928 84002727928' union all
select '84002727924 84002727926 84002727927 84002727928' union all
select '14002727924 14002727926 14002727927 14002727928 84002727928'
select
sum(len(cast(col as nvarchar(2000)))-
len(replace(cast(col as nvarchar(2000)),' ',''))+1) from @t
/*
19
*/