|
|
|
Title:
|
Parse a list of integers usf_ParseIntegerArray
|
Language:
|
SQL
|
|
Description:
|
Takes a comma delimited list of integers as its input, returns a table listing those integers in a single column.
|
Views:
|
32
|
|
Author:
|
Matthew Briggs
|
Date Added:
|
1/26/2012
|
Copy
|
Code
|
|
CREATE FUNCTION dbo.usf_ParseIntegerArray
(
@IntArray VARCHAR(MAX)
)
RETURNS
@c TABLE (Col INT NOT NULL PRIMARY KEY)
AS
BEGIN
--remove whitespace
SET @IntArray = ','+@IntArray+','
SET @IntArray = REPLACE(@IntArray,CHAR(13),'')
SET @IntArray = REPLACE(@IntArray,CHAR(10),'')
SET @IntArray = REPLACE(@IntArray,CHAR(9) ,'')
SET @IntArray = REPLACE(@IntArray,CHAR(9) ,'')
SET @IntArray = REPLACE(@IntArray, ' ' ,'')
DECLARE
@x INT
, @e INT
, @int INT
, @CurrentCommaPos INT
, @NextCommaPos INT
, @intLength INT
SET @x = 1
SET @e = dbo.usf_CountChar(@IntArray,',') - 1
SET @CurrentCommaPos = 1
SET @NextCommaPos = 2
WHILE @x <= @e
BEGIN
SET @NextCommaPos = CHARINDEX(',',@IntArray,@CurrentCommaPos+1)
SET @intLength = @NextCommaPos - (@CurrentCommaPos+1)
SET @int =
CAST
(
SUBSTRING
(
@IntArray
,@CurrentCommaPos+1
,@intLength
)
AS INT
)
IF @int NOT IN (SELECT Col FROM @c) AND @int IS NOT NULL INSERT INTO @c VALUES (@int)
SET @CurrentCommaPos = @NextCommaPos
SET @x = @x + 1
END
RETURN
END
;
GO
|
|
Usage
|
SELECT Col FROM dbo.usf_ParseIntegerArray('1,2,3,65,25,54')
|
|
|