示例：

SET NOcount ON

create TABLE ##Table([keyCol] varchar(3), [NewValues] varchar(max))

–原数据

select

[title_id]

,[title]

FROM [pubs].[dbo].[titles]

where [type] like ’p%’

–以title_id的前两个字符为参考键值，合并title到一个临时表中

insert INTO ##Table

&#101xecute [ChineseHoliday].[Helper].[joinValue]

@TableName = ’[pubs].[dbo].[titles]’

,@KeyColName = ’LEFT([title_id], 2)’

,@joinColName = ’’’《’’+[title] + ’’》’’’

,@Quote = ’,’

,@where = ’[type] like ’’p%’’’

–显示

select * FROM ##Table

–对临时表NewValues的值进行分拆

&#101xecute [ChineseHoliday].[Helper].[SpliteValues]

@TableName = ’##Table’

,@KeyColName = ’[keyCol]’

,@SpliteColName = ’[NewValues]’

,@Quote = ’,’

–删除临时表

drop TABLE ##Table

结果：

title_id title

——– ——————————————————————————-

PC1035 But Is It User Friendly?

PC8888 Secrets of Silicon Valley

PC9999 Net Etiquette

PS1372 Computer Phobic AND Non-Phobic Individuals: Behavior Variations

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 行受影响)