用自定义函数剃除字段中符串中大于9位以上的数字

问题贴:http://topic.youkuaiyun.com/u/20100329/19/222fc2d6-e65f-40c1-af8a-ca577a7a510e.html?seed=1857721250

 

--把字段中连续9位的数字都删除掉

--------------------------------------------------------------------------

--  Author : htl258(Tony)

--  Date   : 2010-03-29 19:41:17

--  Version:Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (Intel X86)

--          Mar 29 2009 10:27:29

--          Copyright (c) 1988-2008 Microsoft Corporation

--          Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 2)

--------------------------------------------------------------------------

--> 生成测试数据表:tb

 

IF NOT OBJECT_ID('[tb]') IS NULL

    DROP TABLE [tb]

GO

CREATE TABLE [tb]([a] NVARCHAR(100))

INSERT [tb]

SELECT 'Mar13845673456CopyrightQQ:123456789Corporation' UNION ALL

SELECT 'abc258521123226MicrosoftQQ:2541254876587' UNION ALL

SELECT 'acd2585WindowsQQ:2541254876587' UNION ALL

SELECT '258ded2585EditionQQ:2541'

GO

--SELECT * FROM [tb]

 

-->SQL查询如下:

if OBJECT_ID('fn_tony') is not null

    drop function fn_tony

go

create function fn_tony(

@s nvarchar(4000)

)returns nvarchar(4000)

as

begin

    declare @r nvarchar(4000)

    set @r=''

    while len(@s)>0 

    begin

       if len(left(@s,patindex('%[^0-9]%',@s+'a')-1))<9

       set @r=@r+left(@s,patindex('%[^0-9]%',@s+'a')-1)                     

       set @s=stuff(@s,1,patindex('%[^0-9]%',@s+'a')-1,'') 

       set @r=@r+left(@s,patindex('%[0-9]%',@s+'0')-1)

       set @s=stuff(@s,1,patindex('%[0-9]%',@s+'0')-1,'')

    end   

    return @r

end

go

 

update tb set a=dbo.fn_tony(a)

 

select * from tb

/*

a

---------------------------------

MarCopyrightQQ:Corporation

abcMicrosoftQQ:

acd2585WindowsQQ:

258ded2585EditionQQ:2541

 

(4 行受影响)

*/