SQL to return a table based on string input

Question

Hello everyone, I am a new with T-SQL. So, please help me to write the sql.

I have table Price (Code column is primary column):

Code    Value
A1       234
A2       525 
A3       566

I will input a string and the sql need to return a table.

Ex1: input 'A2' -> return:

Code    Value
A2       525

Ex2: input 'A1 A3' -> return:

Code    Value
A1       234
A3       566

Ex3: input 'A1 A3 A1' -> return:

Code    Value
A1       234
A3       566

Ex4: input 'A1 A4' -> return:

Code    Value
A1       234

Please help me. I am using SQL Server 2005. Tks.

Answer 1

SELECT [Price].Code, [Price].Value FROM [Price] WHERE [Price].Code IN ('A1', 'A2');

It's very effective, but it has two limitations:

• You can't use regular SQL parameters in an IN clause, so you'll have append it to your SQL string automatically, which, in some cases, could open SQL Injections.

• It's not exactly the input format you requsted: instead of A2 A2 it's 'A1', 'A2'.

Good luck anyway!

EDIT: If you really want to use the A1 A2 format, you can't use IN and you'd have to split the string and then check if it contains the current [Price].Code. Just note that it will be much less effective than my first example.

T-SQL doesn't support Split by default, so you'll have to add it manually:

CREATE FUNCTION [dbo].[Split]
(    
    @RowData NVARCHAR(MAX),
    @Delimeter NVARCHAR(MAX)
)
RETURNS @RtnValue TABLE 
(
    ID INT IDENTITY(1,1),
    Data NVARCHAR(MAX)
) 
AS
BEGIN 
    DECLARE @Iterator INT
    SET @Iterator = 1

    DECLARE @FoundIndex INT
    SET @FoundIndex = CHARINDEX(@Delimeter,@RowData)

    WHILE (@FoundIndex>0)
    BEGIN
        INSERT INTO @RtnValue (data)
        SELECT 
            Data = LTRIM(RTRIM(SUBSTRING(@RowData, 1, @FoundIndex - 1)))

        SET @RowData = SUBSTRING(@RowData,
                @FoundIndex + DATALENGTH(@Delimeter) / 2,
                LEN(@RowData))

        SET @Iterator = @Iterator + 1
        SET @FoundIndex = CHARINDEX(@Delimeter, @RowData)
    END

    INSERT INTO @RtnValue (Data)
    SELECT Data = LTRIM(RTRIM(@RowData))

    RETURN
END

And then, you could do something like this:

SELECT [Price].Code, [Price].Value FROM [Price] 
JOIN Split(@Codes, ' ') AS [Code] 
 ON [Code].Data = [Price].Code

Here's the source for the Split function.

Answer 2

All the answers you can find in this great article.

Answer 3

As Alon mentioned, you need a function or query to split the values into rows in a table. Another way to do this is with a Numbers table which can be static or created as part of a common table expression:

Declare @Alist varchar(50);
Declare @Delimiter char(1);
Declare @DelimiterLength int;

Set @Delimiter = ' ';
Set @DelimiterLength = DataLength(@Delimiter);
Set @Alist = 'A1 A2 A3';
Set @Alist = @Delimiter + @Alist + @Delimiter;

With Numbers As
    (
    Select Row_Number() Over ( Order By C1.object_id ) As Value
    From sys.columns As C1
        Cross Join sys.columns As C2
    )
Select CharIndex(@Delimiter, @Alist, N.Value) + @DelimiterLength As Position            
    , Substring (
                @Alist
                , CharIndex(@Delimiter, @Alist, N.Value) + @DelimiterLength         
                , CharIndex(@Delimiter, @Alist, N.Value + 1)                            
                    - ( CharIndex(@Delimiter, @Alist, N.Value) + @DelimiterLength ) 
                ) As Value
From Numbers As N
Where N.Value Between 1 And ( Len(@Alist) - 1 )
    And Substring(@Alist, N.Value, @DelimiterLength) = @Delimiter
Order By N.Value

Here the space delimiter presents a small problem. The Len function ignores spaces in its determination so I used the DataLength function and also ensure that @Delimiter was declared as a varchar instead of a nvarchar. DataLength will return the number of bytes in the string which will be twice the number of characters in general for a nvarchar.

The Numbers CTE (or it could be a static table) is just a static list of sequential integers which is quite useful for situations just like this one.

This type of approach can also be incorporated into a general query where you analyze every row in another table like so:

With Numbers As
    (
    Select Row_Number() Over ( Order By C1.object_id ) As Value
    From sys.columns As C1
        Cross Join sys.columns As C2
    )
Select CharIndex(@Delimiter, A.List, N.Value) + @DelimiterLength            
    , Substring (
                A.List
                , CharIndex(@Delimiter, A.List, N.Value) + @DelimiterLength         
                , CharIndex(@Delimiter, A.List, N.Value + 1)                            
                    - ( CharIndex(@Delimiter, A.List, N.Value) + @DelimiterLength ) 
                )
From Numbers As N
    Cross Join ( Select A1.List From SomeTable ) As A
Where N.Value Between 1 And ( Len(A.List) - 1 )
    And Substring(A.List, N.Value, @DelimiterLength) = @Delimiter
Order By N.Value