Get data from comma separated string input

Question

I have a stored procedure where input is a comma separated string say '12341,34567,12446,12997' and it is not sure that the input string always carries numerical data. It may be '12341,34as67,12$46,1we97' so I need to validate them and use only the valid data in query.

Say my query is (Where the column OrderCode is int type)

select * from dbo.DataCollector where OrderCode in (12341,34567,12446,12997)

or only the valid data if other are invalid

select * from dbo.DataCollector where OrderCode in (12341)

For such situation what would be a good solution.

Answer 1

One way that works also in SQl-Server 2005 would be to create a split-function, then you can use ISNUMERIC to check if it's a number:

DECLARE @Input VARCHAR(MAX) = '12341,34as67,12$46,1we97' 

SELECT i.Item FROM dbo.Split(@Input, ',')i
WHERE  IsNumeric(i.Item) = 1

Demo

Your complete query:

select * from dbo.DataCollector 
where OrderCode in ( SELECT i.Item FROM dbo.Split(@Input, ',')i
                     WHERE  IsNumeric(i.Item) = 1 )

Here is the split-function which i use:

CREATE FUNCTION [dbo].[Split]
(
    @ItemList NVARCHAR(MAX), 
    @delimiter CHAR(1)
)
RETURNS @ItemTable TABLE (Item VARCHAR(250))  
AS      

BEGIN    
    DECLARE @tempItemList NVARCHAR(MAX)
    SET @tempItemList = @ItemList

    DECLARE @i INT    
    DECLARE @Item NVARCHAR(4000)

    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 @ItemTable(Item) VALUES(@Item)
        IF @i = 0
            SET @tempItemList = ''
        ELSE
            SET @tempItemList = RIGHT(@tempItemList, LEN(@tempItemList) - @i)
        SET @i = CHARINDEX(@delimiter, @tempItemList)
    END 
    RETURN
END  

Edit according to the comment of Damien that ISNUMERIC has it's issues. You can use this function to check if it's a real integer:

CREATE FUNCTION dbo.IsInteger(@Value VarChar(18))
RETURNS Bit
AS 
BEGIN

  RETURN IsNull(
     (Select Case When CharIndex('.', @Value) > 0 
                  Then Case When Convert(int, ParseName(@Value, 1)) <> 0
                            Then 0
                            Else 1
                            End
                  Else 1
                  End
      Where IsNumeric(@Value + 'e0') = 1), 0)    
END

Here is another example with damien's "bad" input which contains £ and 0d0:

Demo