使用XQuery的nodes()方法实现字符拆分
比如一串字符以某定义符号分隔，现我们需要以定义符号对这串字符进行拆分。可以使用XQuery的nodes()方法来实现。实现可参考到这篇：http://www.cnblogs.com/insus/archive/2012/02/25/2367743.html 。不过Insus.NET把实现的方法写成一个自定义函数，在需要的地方调用即可。
udf_StringSplit
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Insus.NET
-- Create date: 2012-02-26 00:15:00
-- Description: Split the string from the delimiter
-- =============================================
CREATE FUNCTION [dbo].[udf_SplitString]
(
@Value NVARCHAR(MAX),
@Delimiter CHAR(1)
)
RETURNS @SplitResult TABLE ([ID] INT IDENTITY(1,1),[WORD] NVARCHAR(MAX))
AS
BEGIN
DECLARE @xml XML = CAST('<insus>' + REPLACE(@Value,@Delimiter,'</insus><insus>') + '</insus>' AS XML)
INSERT INTO @SplitResult([WORD]) SELECT n.value('.','NVARCHAR(50)') AS w
FROM @xml.nodes('/insus') AS E(n)
RETURN
END
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Insus.NET
-- Create date: 2012-02-26 00:15:00
-- Description: Split the string from the delimiter
-- =============================================
CREATE FUNCTION [dbo].[udf_SplitString]
(
@Value NVARCHAR(MAX),
@Delimiter CHAR(1)
)
RETURNS @SplitResult TABLE ([ID] INT IDENTITY(1,1),[WORD] NVARCHAR(MAX))
AS
BEGIN
DECLARE @xml XML = CAST('<insus>' + REPLACE(@Value,@Delimiter,'</insus><insus>') + '</insus>' AS XML)
INSERT INTO @SplitResult([WORD]) SELECT n.value('.','NVARCHAR(50)') AS w
FROM @xml.nodes('/insus') AS E(n)
RETURN
END
应用自定义函数：
SELECT [ID],[WORD] FROM [dbo].[udf_SplitString]('ad;gdf;gdf;gdf;dfsdf',';')
执行结果：
