Application Example: remove multiple area numbers with area numbers in the field.

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

-- 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.csdn.net/htl258

-- Subject: application instance: remove multiple area numbers with area numbers in front of the phone number field.

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

-- Demand Stickers: http://topic.csdn.net/u/20100428/20/f2572998-099c-463a-a530-707a40606c9c.html? 53227

 

--> Generate a test data table: 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]

 

--> The SQL query is as follows:

-- 1. Create a UDF

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. Query

Select [name], DBO. f_getstrtony (phone) phone from TB

/*

Name phone

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

A 12345678/86919111

B 23950423/34972654/89732456

 

(2 rows affected)

 

*/

 

 

This article from the csdn blog, reproduced please indicate the source: http://blog.csdn.net/htl258/archive/2010/04/28/5540795.aspx