讲解SQL Server2005数据项的分拆与合并

核心提示： PS2091 Is Anger the Enemy? PS2106 Life Without Fear PS3333 Prolonged Data Deprivation: Four Case Studies PS7777 Emotional Security: A New Algorit

PS2091 Is Anger the Enemy?

PS2106 Life Without Fear

PS3333 Prolonged Data Deprivation: Four Case Studies

PS7777 Emotional Security: A New Algorithm

keyCol NewValues

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

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

示例:

定义新行,

create FUNCTION [Const].[NewLine]
(
)
RETURNS nchar(2)
AS
BEGIN
　　DECLARE @Result nchar(2)
　　select @Result = char(13) + char(10)
　　RETURN @Result
END
　　
DECLARE
@Input nvarchar(max)
,@Quote nvarchar(max)
　　SET @Input = N’90
10
20
30
40
50
60’
　　SET @Quote = [Const].NewLine()
　　select * FROM [Func].[Splite] (@Input, @Quote)

结果

ID Value

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

1 90

2 10

3 20

4 30

5 40

6 50

7 60

(7 行受影响)