SQL to return a table based on string input
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.
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.
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
精彩评论