Converting comma delimited varchar string to integers

[cpuphantom]

Hello All,

I need some help. I've done a few Google Searches, and searched this forum but am not seeing a good answer.

Here is what I am trying to do. It's actually kind of simple.

I am trying to get a list of user id's from users (stored in tbMembers) that belong to an office group. tbMembers has a field "office" that holds the id from tbOffices for the office the user is in.

But tbOffices has a field called "corp_group" of type varchar(100) that holds a comma delimited string of office_id's. Those office_id's are of other offices that belong in a group.

so here's what I tried to do:

declare @office_id int -- is the id of the office who's group I want.
set @office_id = 1 -- just as an example.

select id from tbMembers where office in (select corp_group from tbOffices where id = @office_id)

the "corp_group" field in tbOffices would look like this: "1, 16, 22, 45, 86, 102, 54" where each of those numbers is the ID number of a different office.

The result I'm looking for is a listing of ID's from tbMembers for users in those offices.

But what I get is: "Syntax error converting the varchar value '1, 16, 22, 45, 86, 102, 54' to a column of data type int."

Makes sense... "corp_group" is a varchar type, and tbOffices.id is an int.

So my question is: How can I parse that into integers... something that will work? Something like a split? Or a ParseInt...

Thanks for the help!

 

[gmmastros]

Take a look at the FAQ section, particularly this one: faq183-5207

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[cpuphantom]

Thanks... I saw that. But I couldn't use it. My boss is too fricken cheap to want to upgrade past SQL 7. And SQL 7 doesn't support Functions.

I guess I should have mentioned the version in my first post.

So can this only be done with a custom function? I guess so...

 

[gmmastros]

>> My boss is too fricken cheap

If your database isn't bigger than 2 gigs, you could upgrade to MSDE 2000 or SQL Express (both of which are free).

>> So can this only be done with a custom function?

No. You could take the 'guts' of the function and put it in to the same stored procedure that you are doing the rest of your work in.

Good luck.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[cpuphantom]

Hey, thanks for the update on the MSDE 2000 and SQL Express. I will look for them.