最近,项目要求统计所有文章中出现的各支股票次数.文章内容存储在content列中,content为ntext类型.提取了一百行结果,得出下面规律:
1,在文章内容中区分股票可以用股票代码,股票代码为六位数字
2,股票代码都写在括号中,但括号中不一定都是股票代码
3,在括号中的股票代码前可能会出现"沪:","沪A:"等字符
再加上ntext中每个字符都是占用两位长度,即a和阿都是两个字节,这样的话去除上面3中说的内容就比较容易了.
考虑上面因素以后,我决定用游标遍历所有行,在行中先用charindex找出文章中前括号出现的位置,然后加1位进入括号内部,如果第一位不是数字第三位是数字,则删去括号中前两位的内容,如果第一位不是数字第四位是数字,则删去括号中前三位的内容.然后再在得到的新字符串中取括号后的六位,判断这六位如果是数字(股票代码都是数字组成),则输出后并去掉已提取过的括号查找下一括号.如果不是数字则直接查找下一括号.最后遍历所有行,取出文章中出现的所有代码并放入临时表中,最后再对临时表中的内容进行简单统计即可得出结果.
代码如下:
-------------------------------------------------------------------------------
/******提取股票出现的次数******/
create table #text_test_a(symbol varchar(20))
--临时表说明：存放在content中出现过的股票代码
truncate table #text_test_a
/*****创建游标从content列中取出股票代码******/
--由于在一行中可以出现多个股票代码，所以循环
--从每一行中取出所有股票代码
declare cur cursor
for select cast(content as nvarchar(4000)) from news
create table #text_test_a(symbol varchar(20))
--临时表说明：存放在content中出现过的股票代码
truncate table #text_test_a
/*****创建游标从content列中取出股票代码******/
--由于在一行中可以出现多个股票代码，所以循环
--从每一行中取出所有股票代码
declare cur cursor
for select cast(content as nvarchar(4000)) from news
--不能声明ntext数据类型,所以要转换成nvarchar类型
declare @content nvarchar(4000)
open cur
fetch next from cur into @content
while @@fetch_status=0
begin
while charindex('(',@content,0)>0
--如果行中存在括号则进入以下循环
begin
if isnumeric(substring(@content,charindex('(',@content,0)+1,1))=0 and isnumeric(substring(@content,charindex('(',@content,0)+3,1))=1
--由于括号中可能会出现“沪：600001”之类的股票代码，所以去除“沪：”
begin
set @content=stuff(@content,charindex('(',@content,0)+1,2,'')
end
if isnumeric(substring(@content,charindex('(',@content,0)+1,1))=0 and isnumeric(substring(@content,charindex('(',@content,0)+4,1))=1
--由于括号中可能会出现“沪A：600001”之类的股票代码，所以去除“沪A：”
begin
set @content=stuff(@content,charindex('(',@content,0)+1,3,'')
end
if isnumeric(substring(@content,charindex('(',@content,0)+1,6))=0
--如果括号中不为股票代码（即数字,.HK的股票代码也去掉），则忽略此括号，查找行中的下一括号的内容
begin
set @content=stuff(@content,1,charindex('(',@content,0)+1,'')
end
else
--如果是股票代码，则将其放入到临时表中，然后查找行中下一括号中的内容
begin
insert into #text_test_a
select substring(@content,charindex('(',@content,0)+1,6)
set @content=stuff(@content,1,charindex('(',@content,0)+1,'')
--select @content
end
end
fetch next from cur into @content
end
close cur
deallocate cur
declare @content nvarchar(4000)
open cur
fetch next from cur into @content
while @@fetch_status=0
begin
while charindex('(',@content,0)>0
--如果行中存在括号则进入以下循环
begin
if isnumeric(substring(@content,charindex('(',@content,0)+1,1))=0 and isnumeric(substring(@content,charindex('(',@content,0)+3,1))=1
--由于括号中可能会出现“沪：600001”之类的股票代码，所以去除“沪：”
begin
set @content=stuff(@content,charindex('(',@content,0)+1,2,'')
end
if isnumeric(substring(@content,charindex('(',@content,0)+1,1))=0 and isnumeric(substring(@content,charindex('(',@content,0)+4,1))=1
--由于括号中可能会出现“沪A：600001”之类的股票代码，所以去除“沪A：”
begin
set @content=stuff(@content,charindex('(',@content,0)+1,3,'')
end
if isnumeric(substring(@content,charindex('(',@content,0)+1,6))=0
--如果括号中不为股票代码（即数字,.HK的股票代码也去掉），则忽略此括号，查找行中的下一括号的内容
begin
set @content=stuff(@content,1,charindex('(',@content,0)+1,'')
end
else
--如果是股票代码，则将其放入到临时表中，然后查找行中下一括号中的内容
begin
insert into #text_test_a
select substring(@content,charindex('(',@content,0)+1,6)
set @content=stuff(@content,1,charindex('(',@content,0)+1,'')
--select @content
end
end
fetch next from cur into @content
end
close cur
deallocate cur
--
/******统计每支股票出现的次数******/
/******统计每支股票出现的次数******/
select symbol,count(symbol) as coun from #text_test_a
group by symbol
order by symbol desc
本文转自 boyi55 51CTO博客，原文链接：http://blog.51cto.com/boyi55/46102，如需转载请自行联系原作者
