SplitStringToTableInt

by Ivan Gerasimenko

JavaScript

USE [Docflow_test]
GO
/****** Object:  UserDefinedFunction [dbo].[SplitStringToTableInt]    Script Date: 07/25/2014 15:22:22 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/*
** Автор: Belkov S.V.
** Назначение: Функция преобразования строки, связанных параметров в целочисленную таблицу
** Дата создания: 17.12.2013
** 
*/
ALTER FUNCTION [dbo].[SplitStringToTableInt] (
	@ItemList NVARCHAR(MAX)
	,@delimiter CHAR(1) = ','
	)
RETURNS @IDTable TABLE (Item INT)
AS
BEGIN
	DECLARE @tempItemList NVARCHAR(MAX)

	SET @tempItemList = @ItemList

	DECLARE @i INT
	DECLARE @Item NVARCHAR(4000)

	SET @tempItemList = REPLACE(@tempItemList, ' ', '')
	SET @i = CHARINDEX(@delimiter, @tempItemList)

	WHILE (LEN(@tempItemList) > 0)
	BEGIN
		IF @i = 0
			SET @Item = @tempItemList
		ELSE
			SET @Item = LEFT(@tempItemList, @i - 1)

		INSERT INTO @IDTable (Item)
		VALUES (@Item)

		IF @i = 0
			SET @tempItemList = ''
		ELSE
			SET @tempItemList = RIGHT(@tempItemList, LEN(@tempItemList) - @i)

		SET @i = CHARINDEX(@delimiter, @tempItemList)
	END

	RETURN
END