如何清空噪音！

Question

例如表adm
河北省石家庄市13799108451桥东区
13766699986北京市海淀区
山东青岛2987546将记录中的连续数字个数大于6，就将这连续数字清空实现结果为
dm
河北省石家庄市桥东区
北京市海淀区
山东青岛

Answer 1

--提取数字
----------------------------------------------------------------
-- Author  :fredrickhu(我是小F，向高手学习)
-- Date    :2010-01-27 12:02:04
-- Version:
--      Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86) 
-- Nov 24 2008 13:01:59 
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据：[tb]
if object_id('[tb]') is not null drop table [tb]
go 
create table [tb]([dm] varchar(31))
insert [tb]
select '河北省石家庄市13799108451桥东区' union all
select '13766699986北京市海淀区' union all
select '山东青岛2987546'
--------------开始查询--------------------------
IF OBJECT_ID('DBO.GET_NUMBER2') IS NOT NULL
DROP FUNCTION DBO.GET_NUMBER2
GO
CREATE FUNCTION DBO.GET_NUMBER2(@S VARCHAR(100))
RETURNS VARCHAR(100)
AS
BEGIN
WHILE PATINDEX('%[^0-9]%',@S) > 0
BEGIN
set @s=stuff(@s,patindex('%[^0-9]%',@s),1,'')
END
RETURN @S
END
GO
select DBO.GET_NUMBER2(dm) from tb
----------------结果----------------------------
/* 
----------------------------------------------------------------------------------------------------
13799108451
13766699986
2987546(3 行受影响)
*/

Answer 2

楼主 你的记录里 会不会存在
XXXX12334XXXX6666
这样的记录？
还是只会是中文字+数字？

Answer 3

只是中文字+数字
if object_id('[tb]') is not null drop table [tb]
go 
create table [tb]([dm] varchar(31))
insert [tb]
select '河北省石家庄市13799108451桥东区' union all
select '13766699986北京市海淀区' union all
select '1386北京市xiao f区' union all
select '山东青岛2987546'
--------------开始查询--------------------------
IF OBJECT_ID('DBO.GET_NUMBER2') IS NOT NULL
DROP FUNCTION DBO.GET_NUMBER2
GO
CREATE FUNCTION DBO.GET_NUMBER2(@S VARCHAR(100))
RETURNS VARCHAR(100)
AS
BEGIN
declare @s1 varchar(100)
set @s1=@s
WHILE PATINDEX('%[^0-9]%',@S1) > 0
BEGIN
set @s1=stuff(@s1,patindex('%[^0-9]%',@s1),1,'')
END
if(len(@s1)>6)
set @s=replace(@s,@s1,'');
return @s
END
GO
select DBO.GET_NUMBER2(dm) from tb
----------------------------------------------------------------------------------------------------
河北省石家庄市桥东区
北京市海淀区
1386北京市xiao f区
山东青岛

Answer 4

--> Title  : Generating test data 
--> Author : wufeng4552
--> Date   : 2010-01-27 
if not object_id('tb') is null
drop table tb
Go
Create table tb([dm] nvarchar(21))
Insert tb
select N'河北省石家庄市13799108451桥东区' union all
select N'13766699986北京市海淀区' union all
select N'山东青岛2987546'union all
select N'測試147' union all
select N'147' union all
select N'測試'
Go
select
      case when patindex('%[0-9]%',[dm])>0 then
           replace(left([dm]+'a',patindex('%[0-9]%',[dm]+'a')-1)+
              case when len(left(stuff([dm]+'a',1,patindex('%[0-9]%',[dm]+'a')-1,''),
                        patindex('%[^0-9]%',stuff([dm]+'a',1,patindex('%[0-9]%',[dm]+'a')-1,''))-1))>6
                   then      
                       stuff((stuff([dm]+'a',1,patindex('%[0-9]%',[dm]+'a')-1,'')),1,len(left(stuff([dm]+'a',1,patindex('%[0-9]%',[dm]+'a')-1,''),
                       patindex('%[^0-9]%',stuff([dm]+'a',1,patindex('%[0-9]%',[dm]+'a')-1,''))-1)),'')
                   else
                   [dm]+'a'
              end ,'a','')
          else
             [dm]
         endfrom tb
/*
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
河北省石家庄市桥东区
北京市海淀区
山东青岛
測試測試147
147
測試(6 個資料列受到影響)*/

Answer 5

if object_id('[tb]') is not null drop table [tb]
go 
create table [tb]([dm] varchar(31))
insert [tb]
select '河北省石家庄市13799108451桥东区' union all
select '13766699986北京市海淀区' union all
select '山东青岛2987546'
goInsert [tb]
select N'河北省石家庄市13799108451桥东区' union all
select N'13766699986北京市海淀区' union all
select N'山东青岛2987546'union all
select N'測試147' union all
select N'147' union all
select N'測試'
Go--------------开始查询--------------------------
IF OBJECT_ID('DBO.GET_CHINESEWORDS') IS NOT NULL
DROP FUNCTION DBO.GET_CHINESEWORDS
GO
CREATE FUNCTION DBO.GET_CHINESEWORDS(@S VARCHAR(100))
RETURNS VARCHAR(100)
AS
BEGIN
declare @str_len int,@i int,@str_result varchar(100),@charasciicode tinyint,@ischineseword int
set @str_len = datalength(@S)
set @i = 1
set @ischineseword = 0
set @str_result = ""
WHILE @i <= @str_len
BEGIN
  set @charasciicode=ascii(substring(@S,@i,1))
  if @charasciicode > 128 
      begin 
        set @ischineseword = (@ischineseword + 1 ) % 2 
        set @str_result = @str_result + substring(@S,@i,1)        
      end
    
  set @i = @i + 1 
END
RETURN @str_result
END
GO
select DBO.GET_CHINESEWORDS(dm) from tb
go
----------------结果----------------------------
/* 
----------------------------------------------------------------------------------------------------
河北省石家庄市桥东区北京市海淀区山东青岛河北省石家庄市桥东区北京市海淀区山东青岛測試測試
(9 行受影响)
*/

Answer 6

没考虑字符集的问题。用汉字的ascii来判断，不知道是否通用。

Answer 7

create table #(dm nvarchar(4000));
insert into #
 select N'河北省石家庄市13799108451桥东区' union all
 select N'13766699986北京市海淀区' union all
 select N'山东青岛2987546';
-- 缺点：只适用于 dm 中只能包含一个连续数字（大于 6）的字段
select STUFF(dm,PATINDEX('%[0-9][0-9][0-9][0-9][0-9][0-9][0-9]%',dm),
LEN(dm)-PATINDEX('%[0-9][0-9][0-9][0-9][0-9][0-9][0-9]%',dm)-
PATINDEX('%[0-9][0-9][0-9][0-9][0-9][0-9][0-9]%',reverse(dm))+2,'') 
from # where PATINDEX('%[0-9][0-9][0-9][0-9][0-9][0-9[0-9]%',dm)>0