CODEWITHSUNDEEP
sql-server
WilliamK
WilliamK

Reputation: 781

SQL Server : Select Record Where ID Appears In String

I have a table of items and need to select the rows where the item ID is found inside a string formed and retrieved from elsewhere. For example the string to match looks like:

'100684','103223','103228'

or I can strip it to produce:

100684,103223,103228

But I am not getting any success from the following attempts:

SQL = "SELECT * FROM Table Where ID LIKE '" & strString & "'  "
SQL = "SELECT * FROM Table Where ID IN '" & strString & "'  "
SQL = "SELECT * FROM Table Where PATINDEX('%" & ID & "%','" & strString & "') > 0 "
SQL = "SELECT * FROM Table Where PATINDEX(ID,'" & strString & "') > 0 "
SQL = "SELECT * FROM Table Where CHARINDEX(ID, '" & strString & "') > 0 "

These either fail to produce a result or give an error like

Argument data type int is invalid for argument 1 of charindex function

Upvotes: 1

Views: 7484

Answers (3)

StrayCatDBA
StrayCatDBA

Reputation: 2880

Here's a good rule of thumb: Strings passed in as parameters should NEVER be concatenated into the SQL query text.

The correct approach is to use either named parameters, or a table valued parameter. You want to end up with a sql query like this:

 SQL = "SELECT * FROM Table Where id IN (@p1, @p2, @p3)"

which you then call passing in the appropriate values for @p1, @p2, @p3. It's fine to dynamically build the string with varying numbers of @p's.

If you've got more than 2100 values to pass in then you should use a table valued parameter.

Lastly, you can also safely parse strings in T-SQL using a splitter. Jeff Moden has an excellent article on SSC about splitting text efficiently: http://www.sqlservercentral.com/articles/Tally+Table/72993/

Upvotes: 0

Sudip Kafle
Sudip Kafle

Reputation: 4391

A conversion from STRING to INT will help

SQL = "SELECT * FROM Table Where ID = CONVERT(INT,'"&strString &"')"

Upvotes: 1

peterm
peterm

Reputation: 92795

Use stripped version of your string and try

SQL = "SELECT * FROM Table Where ID IN (" & strString & ")"

Upvotes: 2

Related Questions