Solved

Select Records Where Value Is In List

Posted on 2003-11-19
37
507 Views
Last Modified: 2008-02-01
I have a table with a field that contains a comma delimited list of IDs, e.g,

1,4,6,7

I need to write a query that can select those records where a particular number is in that list, e.g.,

SELECT * FROM tblName WHERE 4 IN (SELECT fldCommaDelimited FROM tblName)

The problem is the comma delimited list is of type nvarchar and I get an error saying it cannot convert to type of smallint.  Is there a direct way to do this or do I have to do multiple queries, store to a temp table, etc...
0
Comment
Question by:AzraSound
  • 16
  • 14
  • 7
37 Comments
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 9784605
Perhaps something like this (untested):

Select *
from tblName
Where PatIndex('%,4,%', ',' + fldCommaDelimited + ',") > 0

Anthony
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 9784609
Of course, it should be added that your database would probably benefit from some normalization, making this very inefficient query moot, but I think you know that already.

Anthony
0
 
LVL 28

Author Comment

by:AzraSound
ID: 9784632
Thats a new one on me...what is PatIndex and what exactly is the above command doing?
0
 
LVL 28

Author Comment

by:AzraSound
ID: 9784634
And is that command specific to a particular version of SQL Server?
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 9784682
>>what is PatIndex and what exactly is the above command doing?<<
PatIndex is somewhat like the Visual Basic InStr function, except it has a very limited of Regular Expressions.  It searches for patterns and returns the position. So in the example above I am searching for the string ",4," anywhere (%) in the column fldCommaDelimited with a "," at the front and at the back.

>>And is that command specific to a particular version of SQL Server? <<
I believe it has been there since at least 6.5 and may be longer.

Anthony
0
 
LVL 7

Expert Comment

by:wsteegmans
ID: 9785988
You could also write a little stored procedure (or just drop it in Analyzer), something like this ...

DECLARE @lstIDS as nvarchar(1000)
DECLARE @SQL as nvarchar(1000)

/* Supposing you have one row where all your ID's are stored (in field fldCommaDelimited) */
SET @lstIDS = (SELECT TOP 1 fldCommaDelimited FROM tblNameCommaList)

SET @SQL = 'SELECT * FROM tblName WHERE CONVERT(int, ID) IN (' + @lstIDS + ')'
exec (@sql)
0
 
LVL 28

Author Comment

by:AzraSound
ID: 9787804
>>I am searching for the string ",4,"

So that would not work if 4 was the first or last item in the list?


wsteegmans,
I was hoping there was a direct way w/o resorting to a stored procedure, but, alas, I may have to go that route.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 9788000
>>So that would not work if 4 was the first or last item in the list?<<
Please look at the code I posted (I had a typo the last double quote should have been a quote. I have corrected it here:

Select *
from tblName
Where PatIndex('%,4,%', ',' + fldCommaDelimited + ',') > 0

Notice how I am adding a comma in the front.

But don't believe me, test it for yourself as follows:

Declare @fldCommaDelimited as varchar(100)
Set @fldCommaDelimited = '1,4,6,7'

Select PatIndex('%,4,%', ',' + @fldCommaDelimited + ',')
Select PatIndex('%,1,%', ',' + @fldCommaDelimited + ',')

And then tell me where it fails, rather than jumping to conclusions.

Anthony
0
 
LVL 28

Author Comment

by:AzraSound
ID: 9788081
>>And then tell me where it fails, rather than jumping to conclusions

Just asking for clarification.  I'm not jumping to conclusions.  There is a difference.  I see the code now and what you are doing, but if clarification annoys you, I will make sure not to ask for any in the future.  Thanks.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 9788125
>>I see the code now and what you are doing, but if clarification annoys you, I will make sure not to ask for any in the future. <<
Not in the slightest and I hope I have not offended you, besides I have the utmost respect for you and what you have accomplished here at this site.  I was just taken aback that you chose not to test something as simple as this (and I trust I do not sound condescending).

Anthony
0
 
LVL 28

Author Comment

by:AzraSound
ID: 9788193
I think my hesitance in testing was due to that fact that the sample I layed out initially, and your response, is not exactly how my SQL statement is currently structured, and I wasn't sure I could convert it to work in my scenario.  Actual statement as it stands:

SELECT PackageID FROM tblPackages WHERE PackageID IN (SELECT HostingPackages FROM tblPackages) ORDER BY PackageName ASC


So none of the numbers are hardcoded, and the delimited list is pulled in a SELECT as well.  How could I take this existing query and use the PatIndex function?  Would it require multiple steps?  I'm not sure of the syntax (or if its even possible) to do something like:

... WHERE PatIndex('%, + PackageID + %', ',' + (SELECT HostingPackages ...) + ',')

Is there a way to pull something like that off?
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 9788201
This may be overkill, but supposing over in the Visual Basic TA someone was asking about searching for the string 'ac' in the string 'acperkins' and you suggested something like:

?InStr(1,"acperkins", "ac", vbTextCompare)

They then came back asked for an explanation of InStr and you gave it. Finally they responded with something like :

"So that would not work if you used 'AC' instead of 'ac'?"

You would or should be forgiven a momentary lapse.

Do you get my point? Or am I being as tactless as the proverbial bull in a china shop?  Feel free to say yes <g>

Anthony
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 9788212
Our messages crossed.  Let me take a look at your last one.

Anthony
0
 
LVL 28

Author Comment

by:AzraSound
ID: 9788230
Anthony, no worries.  I can understand your frustration with me, but next time, just tell me to get my foot out of my a**, grab a cup of coffee, and check back into reality.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 9788310
(Actually it is I that needs the coffee!)

First of all, yes you can use dynamic SQL (the Exec command) as suggested above, but dynamic SQL should only be used as a last resort.

So instead try this (again untested):

Declare @HostingPackages varchar(1000)     -- Set the length and datatype appropriately

-- Following should only return one row:
SELECT @HostingPackages = HostingPackages
FROM tblPackages
Where YourConditionGoesHere

SELECT PackageID
FROM tblPackages
WHERE PatIndex('%,4,%', ',' + @HostingPackages + ',') > 0

I realize this may not be exactly what you wanted, but first see if it does what you need.  Than if it does, perhaps we can simplify it down to one statement.

I notice that you are using PackageID, this I am asuming is a constant.  If this is not the case or this is way off track, do me a favor and post some sample data from both tables and what your output should look like.

Hopefully, by then I will be able to get my coffee and my brain cells working!

Anthony
0
 
LVL 28

Author Comment

by:AzraSound
ID: 9788396
That is how I feel I need to do this right now (I am writing some asp pages).  So, as of write now, the only way I could figure to do it would be:

Dim strList
Dim rs

'grab list of hosting packages based on selection
Set rs = adoConn.Execute("SELECT HostingPackages FROM tblPackages WHERE PackageID = " & lngPackageID)

'store list
strList = rs.Fields("HostingPackages")

'grab individual packages
Set rs = adoConn.Execute("SELECT PackageID FROM tblPackages WHERE PackageID IN (" & strList & ") ORDER BY PackageName ASC")



Sample data:

PackageID    PackageName    PackageType    HostingPackages
1                 Package1          HOSTING          
2                 Package2          HOSTING
3                 Package3          DESIGN             1,2
4                 Package4          DESIGN             1,2,5
5                 Package5          HOSTING



Essentially, given a selected design package, I need to pull out each individual record of hosting packages associated with it.  So, I present to the user a list of design packages (in the sample set, packages 3 and 4).  If they select 3, I want a recordset with packages 1 and 2 in it, and if they select 4, I want a recordset with packages 1, 2 and 5 in it.  I hope that makes sense.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 9788479
>>'grab list of hosting packages based on selection
Set rs = adoConn.Execute("SELECT HostingPackages FROM tblPackages WHERE PackageID = " & lngPackageID)<<

So that I don't jump to conclusions, please tell me if the above only returns one row (what is throwing me off is your comment "grab list", as if there was more than one).

Anthony
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 9788498
Also, does this code return the right data:
Declare @HostingPackages varchar(1000)     -- Set the length and datatype appropriately

-- Following should only return one row:
SELECT @HostingPackages = HostingPackages
FROM tblPackages
Where YourConditionGoesHere

SELECT PackageID
FROM tblPackages
WHERE PatIndex('%,4,%', ',' + @HostingPackages + ',') > 0

Anthony
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 7

Expert Comment

by:wsteegmans
ID: 9788505
Seems that the solutions is comming to my comment ... (but still a SP, but general, not hard coded)
But, just create a COMMAND in your ASP, Excecute it and fill the Recordset ...

Call it by
exec gethostingpackages '1'  /* Where '1' is your PackageID ... */

CREATE PROCEDURE GetHostingPackages @pPackageID nvarchar(50)  AS
DECLARE @lstIDS as nvarchar(1000)
DECLARE @SQL as nvarchar(1000)

SET @lstIDS = (SELECT HostingPackages FROM tblPackages WHERE PackageID = @pPackageID)

SET @SQL = 'SELECT * FROM tblPackages WHERE CONVERT(int, PackageID) IN (' + @lstIDS + ')'
exec (@sql)
GO

Hope this helps ...

Regards.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 9788566
>>Seems that the solutions is comming to my comment<<
Trust me I would only suggest Dynamic SQL as a last resort.

>>but still a SP, but general<<
"I was hoping there was a direct way w/o resorting to a stored procedure, but, alas, I may have to go that route. "

Anthony
0
 
LVL 28

Author Comment

by:AzraSound
ID: 9788683
The list I am referring to is just the delimited list in the HostingPackages field for the selected PackageID, so it should be one record, with one field returned (e.g., 1,2).  The PackageID is an auto-incremented identity field, so yes, it is unique.

I am trying your suggestion, but I am a bit confused (again) when we get to the PatIndex portion.  Ok, your first statement is right on.  We get the list of HostingPackages (say 1,2,5).  Now, the next statement needs to grab these three records into a recordset (e.g., a recordset containing three records for packages 1,2, and 5), but it seems I would have to specify a hosting package's ID (the 4) to get a list of packages where package ID 4 is a hosting package.  Did I say that right?  Am I helping clarify or just causing more confusion?
0
 
LVL 28

Author Comment

by:AzraSound
ID: 9788706
I am getting the hosting packages back as nvarchar, but is there a way to strip the single quotes, because that's what throws the whole thing, e.g,

SELECT PackageID FROM tblPackages WHERE PackageID IN ('1,2,5')   'fails
SELECT PackageID FROM tblPackages WHERE PackageID IN (1,2,5)     'succeeds

Would the replace function work here?
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 9788829
Try this without a Stored Procedure (untested):

Dim rs, SQL, PackageID

PackageID = CStr(lngPackageID)
SQL = "Declare @HostingPackages varchar(1000);" & _
           "SET NOCOUNT ON;" & _
           "SELECT @HostingPackages = HostingPackages FROM tblPackages Where PackageID = " & PackageID;" & _
           "SELECT PackageID FROM tblPackages WHERE PatIndex('%," & PackageID & ",%', ',' + @HostingPackages + ',') > 0 ORDER BY PackageName ASC"
           
'grab individual packages
Set rs = adoConn.Execute(SQL)

Anthony
0
 
LVL 28

Author Comment

by:AzraSound
ID: 9788923
I get nothing in return because of the last SQL statement and PatIndex using the same PackageID value.  Refer back to my example data set above.  The first statement needs to return the HostingPackages...we all agree on this.  The next statement should return those HostingPackages in a recordset.  So here is the original data:

PackageID    PackageName    PackageType    HostingPackages
1                 Package1          HOSTING          
2                 Package2          HOSTING
3                 Package3          DESIGN             1,2
4                 Package4          DESIGN             1,2,5
5                 Package5          HOSTING


The chosen package ID is 4 (let's say) so the first SQL query should return "1,2,5" which it does, and we all agree on that.  The next statement, however, should return:

PackageID    PackageName    PackageType    HostingPackages
1                 Package1          HOSTING          
2                 Package2          HOSTING
5                 Package5          HOSTING

Essentially, all of the packages specified in that HostingPackages list.
0
 
LVL 7

Expert Comment

by:wsteegmans
ID: 9789028
If you want to use the PatIndex, change the code like this:

SQL = "Declare @HostingPackages varchar(1000);" & _
           "SET NOCOUNT ON;" & _
           "SELECT @HostingPackages = HostingPackages FROM tblPackages Where PackageID = " & PackageID;" & _
           "SELECT PackageID FROM tblPackages WHERE PatIndex('%,' + CONVERT(nvarchar,PackageID) + ',%', ',' + @HostingPackages + ',') > 0 ORDER BY PackageName ASC"

Regards!
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 9789038
Evidently the coffee has not kicked in:
Change this line:
          "SELECT PackageID FROM tblPackages WHERE PatIndex('%," & PackageID & ",%', ',' + @HostingPackages + ',') > 0

To (PackageID now referes to the int column and therefore needs to be cast to a varchar):
"SELECT * FROM tblPackages WHERE PatIndex('%,' + Cast(PackageID as varchar(20)) + ',%', ',' + @HostingPackages + ',') > 0 ORDER BY PackageName ASC"

If you need an explanation as to what I am doing, let me know.

Anthony
0
 
LVL 28

Author Comment

by:AzraSound
ID: 9789054
>>CONVERT(nvarchar,PackageID)

Wouldn't that give me '2' but the list still contains items like 1,2,5 and not '1','2','5'?  I know my SQL is rusty but isn't that how the list needs to be in that case?

Also, as mentioned, the PatIndex approach does not appear to use the correct logic, as I am not looking for a particular package id in the list of hosting packages returned, but rather, I just want to grab all of those packages listed in hosting packages (see my last comment).
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 9789098
>>Wouldn't that give me '2' but the list still contains items like 1,2,5 and not '1','2','5'? <<
No. It is the PackageID column and therfore it includes all the values from that column that are in the 1,2,5 string.

Anthony
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 125 total points
ID: 9789117
Do yourself a favor, run the following script in SQL Analyzer and see the results. I have now tested it (should have done this in the first place, rather than wasting your time) and it works as advertised:
 
Declare @HostingPackages varchar(1000);
SET NOCOUNT ON
SELECT @HostingPackages = HostingPackages FROM tblPackages Where PackageID = 4
SELECT * FROM tblPackages WHERE PatIndex('%,' + Cast(PackageID as varchar(20)) + ',%', ',' + @HostingPackages + ',') > 0 ORDER BY PackageName ASC

Anthony
0
 
LVL 28

Author Comment

by:AzraSound
ID: 9789177
I ran the above and it only returned one of the packages specified in HostingPackages, when it should have returned two.  In my actual database, PackageID 1 has in its HostingPackages field '4,5' and after running the above, I only got PackageID 4 back.
0
 
LVL 7

Assisted Solution

by:wsteegmans
wsteegmans earned 125 total points
ID: 9789182
Was writing the same as Anthony ...

CONVERT(nvarchar,PackageID) gives just 2 as a nvarchar. So, that means we have a NVARCHAR holding the value 2.

The first query returns the HostingPackages in form x,x,x as nvarchar.

What we do in the following SELECT statement, we concatinate strings ... so if we make the exercise for Package 4, the PatIndex will hold this:

For every record:

For the first record:  WHERE PatIndex('%,1,%', ',1,2,5,') <- selected
For the 2nd record:  WHERE PatIndex('%,2,%', ',1,2,5,') <- selected
For the 3rd record:  WHERE PatIndex('%,3,%', ',1,2,5,')
For the 4th record:  WHERE PatIndex('%,4,%', ',1,2,5,')
For the 5th record:  WHERE PatIndex('%,5,%', ',1,2,5,') <- selected

Hope it clears it up for you ...

Kind Regards
0
 
LVL 7

Expert Comment

by:wsteegmans
ID: 9789200
>>  PackageID 1 has in its HostingPackages field '4,5' and after running the above, I only got PackageID 4 back
Check if you copied the code the exact way

WHERE PatIndex('%,' + CONVERT(nvarchar,PackageID) + ',%', ',' + @HostingPackages + ',') > 0

The ',' are very importent, also between @HostingPackages ...

Can you check this?
0
 
LVL 28

Author Comment

by:AzraSound
ID: 9789203
Query I ran against my real data:

Declare @HostingPackages varchar(1000);
SET NOCOUNT ON
SELECT @HostingPackages = HostingPackages FROM cccPackages Where PackageID = 1
SELECT * FROM cccPackages WHERE PatIndex('%,' + Cast(PackageID as varchar(20)) + ',%', ',' + @HostingPackages + ',') > 0 ORDER BY PackageName ASC


Returned:

4           Hosting Package 1         Sample #4




However, PackageID 1 has two hosting packages associated with it (4 and 5) and so I need both of those records returned.
0
 
LVL 7

Expert Comment

by:wsteegmans
ID: 9789317
Check if there are no spaces in your HostingPackages field ... ??
Like 4,_5 where _ is a space ... it must for this query -> 4,5
0
 
LVL 28

Author Comment

by:AzraSound
ID: 9789331
Sure enough, that was the problem.  Well, I appreciate all the help here.  I may not actually use this implementation, but I definitely learned some new things, so thanks for your time and patience with me.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 9790199
Sorry I got side-tracked at work.  Glad you got it resolved and thanks wsteegmans for your help.

One thing I pointed out at first was that this database structure could do with some normalization.  I realize this may not be feasible, but as you can see it would certainly help here.

Anthony
0
 
LVL 7

Expert Comment

by:wsteegmans
ID: 9790406
Anthony,

You're absolutely right! A field with comma-separated values pointing to ID's is not very normalized and relational! So, use a Sub-Table where you store HostingPackages. For every HostingPackage connected to a PackageID, create one record. So you can JOIN both tables ...

General tip:
When you're getting troubles with creating simple SQL-Statements, just have a critical look @ your DB-structure!
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Suggested Solutions

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to shrink a transaction log file down to a reasonable size.

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now