Splitting A String into Multiple Values in SQL Server 2000

I'm asking the question on behalf of someone that works for my client that asked me this. I'm actually more familiar with mySQL than SQL Server, but unfortunately, SQL Server is what the client has used for years.

The question basically this: Is there a way in SQL Server to split a string into multiple values (e.g. array?) that can be used in a WHERE statement.

Here's a PHP example of what I'm talking about.

<?php
    $string = "10,11,12,13";
    $explode = explode(",", $string);
?>

$explode would be equal to array(10,11,12,13). What I need to do is something like this:

SELECT {long field list] FROM {tables} WHERE hour IN SPLIT(",", "10,11,12,13")

With SPLIT being my pseudo-code function that performs the splitting

The reason why I'm not doing this in, let's say, PHP, is because the query is being constructed by reporting software where we can't perform logic (such as my PHP code) before sending it to the database, and the multiple values are being returned by the software as a single string separated by pipes (|).

Unfortunately I do not have access to the reporting software (I think he said it was called Logi or LogiReports or something) or the query my associate was drafting up, but all that开发者_如何学Go is really important for this question is the WHERE clause.

Any ideas?

Dynamic SQL can be used:

declare @in varchar(10)
set @in = '10,11,12,13'
exec ('SELECT {long field list] FROM {tables} WHERE hour IN (' + @in + ')')

Several methods here: Arrays and list in SQL Server

For short strings, I prefer a numbers table

I could copy/paste from here but it really is worth reading

You could use a Function which receives a string containing the "id's" separated by pipes, and return it as a table, which you can query and use in a subquery maybe, like this:

SELECT {long field list] FROM {tables} WHERE hour IN 
(SELECT OrderID from dbo.SplitOrderIDs('2001,2002'))


ALTER FUNCTION [dbo].[SplitOrderIDs]
(
@OrderList varchar(500)
)
RETURNS 
@ParsedList table
(
OrderID int
)
    AS
    BEGIN
    DECLARE @OrderID varchar(10), @Pos int

SET @OrderList = LTRIM(RTRIM(@OrderList))+ ','
SET @Pos = CHARINDEX(',', @OrderList, 1)

IF REPLACE(@OrderList, ',', '') <> ''
BEGIN
    WHILE @Pos > 0
    BEGIN
        SET @OrderID = LTRIM(RTRIM(LEFT(@OrderList, @Pos - 1)))
        IF @OrderID <> ''
        BEGIN
            INSERT INTO @ParsedList (OrderID) 
            VALUES (CAST(@OrderID AS int)) --Use Appropriate conversion
        END
        SET @OrderList = RIGHT(@OrderList, LEN(@OrderList) - @Pos)
        SET @Pos = CHARINDEX(',', @OrderList, 1)

    END
END 

RETURN
END

you can use this stored procedure.
I hope that be useful for you.

CREATE PROCEDURE SP_STRING_SPLIT (@String varchar(8000),@Separator Char(10),@pos_select int=0)

AS

BEGIN

SET NOCOUNT ON

DECLARE @Caracter varchar(8000)

DECLARE @Pos int

Set @Pos=1

Set @Caracter=''

CREATE TABLE #ARRAY
    (   String  varchar(8000)   NOT NULL,
        Pos     int         NOT NULL IDENTITY (1, 1)

    )


While (@Pos<=len(@String))
Begin

    If substring(@String,@Pos,1)=Ltrim(Rtrim(@Separator))
        Begin

            INSERT INTO #ARRAY SELECT @Caracter
            SET @Caracter=''
        End
    Else
        Begin   
            --forma la palabra}
            Set @Caracter=@Caracter+substring(@String,@Pos,1)

        End


    If @Pos=len(@String)
        Begin
            INSERT INTO #ARRAY SELECT @Caracter
        End

        SET @Pos=@Pos+1

    End

     SELECT Pos,String FROM #ARRAY where (Pos=@pos_select Or @pos_select=0)
END

 GO

 exec SP_STRING_SPLIT  'HELLO, HOW ARE YOU?',',',0