Insus.NET以前有用XQuery的nodes()方法寫過一個函數,是MS SQL字元拆分的函數,
今天學習SQL,再嘗試另寫一個:
![](https://img.laitimes.com/img/9ZDMuAjOiMmIsIjOiQnIsISPrdEZwZ1Rh5WNXp1bwNjW1ZUba9VZwlHdsATOfd3bkFGazxCMx8VesATMfhHLlN3XnxCMwEzX0xiRGZkRGZ0Xy9GbvNGLpZTY1EmMZVDUSFTU4VFRR9Fd4VGdsYTMfVmepNHLrJXYtJXZ0F2dvwVZnFWbp1zczV2YvJHctM3cv1Ce-YWan5SOhJDMzYmZlRDMhFWMxIzN5QTZmVGNzImM4ETOiRGN48CXyAzLchDMxIDMy8CXn9Gbi9CXzV2Zh1WavwVbvNmLvR3YxUjL4M3Lc9CX6MHc0RHaiojIsJye.gif)
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Insus.NET
-- Create date: 2013-06-30
-- Description: split string function.
-- =============================================
CREATE FUNCTION [dbo].[udf_Split](@value NVARCHAR(MAX), @delimiter CHAR(1))
RETURNS @Results TABLE ([ID] INT IDENTITY(1,1),[WORD] NVARCHAR(MAX))
AS
BEGIN
DECLARE @index INT
DECLARE @slice NVARCHAR(MAX)
SET @index = 1
IF @value IS NULL
RETURN
WHILE @index <> 0
BEGIN
SET @index = CHARINDEX(@delimiter, @value)
IF @index !=0
SET @slice = LEFT(@value,@index - 1)
ELSE
SET @slice = @value
INSERT INTO @Results([WORD]) VALUES(@slice)
SET @value = RIGHT(@value,LEN(@value) - @index)
IF LEN(@value) = 0 BREAK
END
RETURN
END
GO
udf_Split
例子示範: