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_getstrtony(phone) phone FROM TB
/*
name phone
---------- ------------------------------------
a 12345678/86919111
b 23950423/34972654/89732456
(2 行受影响)
*/
本文来自CSDN博客,转载请标明出处:http://blog.csdn.net/htl258/archive/2010/04/28/5540795.aspx