SQL Server2005数据项的分拆与合并

核心提示：PC 《But Is It User Friendly?》,《Secrets of Silicon Valley》,《Net Etiquette》PS 《Computer Phobic AND Non-Phobic Individuals: Behavior Variations》,《Is Anger the Enem

PC 《But Is It User Friendly?》,《Secrets of Silicon Valley》,《Net Etiquette》

PS 《Computer Phobic AND Non-Phobic Individuals: Behavior Variations》,《Is Anger the Enemy?》,《Life Without Fear》,《Prolonged Data Deprivation: Four Case Studies》,《Emotional Security: A New Algorithm》

KeyCol NewValue

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

PC 《But Is It User Friendly?》

PC 《Secrets of Silicon Valley》

PC 《Net Etiquette》

PS 《Computer Phobic AND Non-Phobic Individuals: Behavior Variations》

PS 《Is Anger the Enemy?》

PS 《Life Without Fear》

PS 《Prolonged Data Deprivation: Four Case Studies》

PS 《Emotional Security: A New Algorithm》

继续:字符串的分拆

-- =============================================

-- Author: LzmTW

-- create date: 20080108

-- Description: 拆分字符串

-- =============================================

create FUNCTION [Func].[Splite]
(
@Input nvarchar(max)
,@Quote nvarchar(max)
)
RETURNS
@Table TABLE
(
[ID] int identity(1,1) PRIMARY KEY
,[Value] nvarchar(max)
)
AS
BEGIN
insert @Table
select
[Value] = NewValue
FROM
(
select
SpliteCol = CONVERT(
xml
,N’’ + REPLACE(
@Input
,@Quote
,N’’) + N’’)
) a
OUTER APPLY
(
select NewValue = N.v.value(N’.’, ’nvarchar(max)’)
FROM SpliteCol.nodes(N’/root/v’) N(v)
) b
RETURN
END