Select Records Where Value Is In List

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...
LVL 28
AzraSoundAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Anthony PerkinsCommented:
Perhaps something like this (untested):

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

Anthony
0
Anthony PerkinsCommented:
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
AzraSoundAuthor Commented:
Thats a new one on me...what is PatIndex and what exactly is the above command doing?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

AzraSoundAuthor Commented:
And is that command specific to a particular version of SQL Server?
0
Anthony PerkinsCommented:
>>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
wsteegmansCommented:
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
AzraSoundAuthor Commented:
>>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
Anthony PerkinsCommented:
>>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
AzraSoundAuthor Commented:
>>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
Anthony PerkinsCommented:
>>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
AzraSoundAuthor Commented:
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
Anthony PerkinsCommented:
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
Anthony PerkinsCommented:
Our messages crossed.  Let me take a look at your last one.

Anthony
0
AzraSoundAuthor Commented:
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
Anthony PerkinsCommented:
(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
AzraSoundAuthor Commented:
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
Anthony PerkinsCommented:
>>'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
Anthony PerkinsCommented:
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
wsteegmansCommented:
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
Anthony PerkinsCommented:
>>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
AzraSoundAuthor Commented:
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
AzraSoundAuthor Commented:
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
Anthony PerkinsCommented:
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
AzraSoundAuthor Commented:
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
wsteegmansCommented:
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
Anthony PerkinsCommented:
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
AzraSoundAuthor Commented:
>>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
Anthony PerkinsCommented:
>>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
Anthony PerkinsCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
AzraSoundAuthor Commented:
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
wsteegmansCommented:
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
wsteegmansCommented:
>>  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
AzraSoundAuthor Commented:
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
wsteegmansCommented:
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
AzraSoundAuthor Commented:
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
Anthony PerkinsCommented:
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
wsteegmansCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.