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.

It would be far better to pass multiple values to your stored procedure using a data type that natively supports multiple values - that would be XML or a table-valued parameter. — Damien_The_Unbeliever
– Damien_The_Unbeliever, Commented Mar 13, 2014 at 13:26

Tim Schmelter · Accepted Answer · 2014-03-13 14:21:54Z

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

edited Mar 13, 2014 at 14:21

answered Mar 13, 2014 at 13:27

Tim Schmelter

462k79 gold badges719 silver badges980 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Damien_The_Unbeliever Over a year ago

Of course, ISNUMERIC has its own issues because £ and 0d0 are acceptable to it.

Tim Schmelter Over a year ago

@Damien_The_Unbeliever: good point, added another function which should exclude these characters.

Collectives™ on Stack Overflow

Get data from comma separated string input

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related