【叶子函数分享三十五】去掉字段中多个带区号电话号码前面的区号

--原帖地址：http://blog.youkuaiyun.com/htl258/archive/2010/04/28/5540795.aspx

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

-- Author : htl258(Tony)

-- Date : 2010-04-28 23:22:15

-- Version:Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)

-- Jul 9 2008 14:43:34

-- Copyright (c) 1988-2008 Microsoft Corporation

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

-- Blog : http://blog.youkuaiyun.com/htl258

-- Subject: 应用实例：去掉字段中多个带区号电话号码前面的区号

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

--需求贴：http://topic.youkuaiyun.com/u/20100428/20/f2572998-099c-463a-a530-707a40606c9c.html?53227

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

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

DROP TABLE [tb]

GO

CREATE TABLE [tb]([name] NVARCHAR(10),[phone] NVARCHAR(40))

INSERT [tb]

SELECT 'a',N'010-12345678/0571-86919111' UNION ALL

SELECT 'b',N'020-23950423/0756-34972654/023-89732456'

GO

--SELECT * FROM [tb]

-->SQL查询如下:

--1.创建自定义函数

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

DROP FUNCTION [f_getphone]

GO

CREATE FUNCTION f_getphone(@s varchar(200))

RETURNS varchar(200)

AS

BEGIN

SET @s=@s+'/'

DECLARE @r VARCHAR(200)

WHILE CHARINDEX('/',@s)>0

SELECT @r=ISNULL(@r+'/','')

+LEFT(STUFF(@s,1,CHARINDEX('-',@s),'')

,CHARINDEX('/',@s)-CHARINDEX('-',@s)-1)

,@s=STUFF(@s,1,CHARINDEX('/',@s),'')

RETURN @r

END

GO

--2.查询

SELECT [name],dbo.f_getphone(phone) 'phone' FROM TB

/*

name phone

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

a 12345678/86919111

b 23950423/34972654/89732456

(2 行受影响)

*/

--本文来自优快云博客

--转载请标明出处：--http://blog.youkuaiyun.com/htl258/archive/2010/04/28/5540795.aspx