47 lines
878 B
MySQL
47 lines
878 B
MySQL
|
USE [SSASAP_NJLS]
|
||
|
|
GO
|
||
|
|
|
||
|
|
SET ANSI_NULLS ON
|
||
|
|
GO
|
||
|
|
|
||
|
|
SET QUOTED_IDENTIFIER ON
|
||
|
|
GO
|
||
|
|
|
||
|
|
CREATE FUNCTION [dbo].[fn_Split]
|
||
|
|
(
|
||
|
|
@Line nvarchar(MAX),
|
||
|
|
@SplitOn nvarchar(5) = ','
|
||
|
|
)
|
||
|
|
RETURNS @RtnValue table
|
||
|
|
(
|
||
|
|
RowID INT NOT NULL IDENTITY(1,1) PRIMARY KEY CLUSTERED,
|
||
|
|
Value varchar(100) NOT NULL
|
||
|
|
)
|
||
|
|
AS
|
||
|
|
BEGIN
|
||
|
|
IF @Line IS NULL RETURN
|
||
|
|
|
||
|
|
DECLARE @split_on_len INT
|
||
|
|
DECLARE @start_at INT
|
||
|
|
DECLARE @end_at INT
|
||
|
|
DECLARE @data_len INT
|
||
|
|
DECLARE @Data varchar(100)
|
||
|
|
set @split_on_len = LEN(@SplitOn)
|
||
|
|
set @start_at = 1
|
||
|
|
|
||
|
|
WHILE 1=1
|
||
|
|
BEGIN
|
||
|
|
SET @end_at = CHARINDEX(@SplitOn,@Line,@start_at)
|
||
|
|
SET @data_len = CASE @end_at WHEN 0 THEN LEN(@Line) ELSE @end_at-@start_at END
|
||
|
|
INSERT INTO @RtnValue (Value) VALUES( SUBSTRING(@Line,@start_at,@data_len) );
|
||
|
|
IF @end_at = 0 BREAK;
|
||
|
|
SET @start_at = @end_at + @split_on_len
|
||
|
|
END
|
||
|
|
|
||
|
|
RETURN
|
||
|
|
END
|
||
|
|
|
||
|
|
GO
|
||
|
|
|
||
|
|
|