Please start any new threads on our new site at We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 SQL parsing question
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Starting Member

18 Posts

Posted - 08/04/2005 :  15:50:20  Show Profile  Reply with Quote
I know this should be simple, but for some reason i am having difficulties! I have a table that has 2 columns i am interested in, Computer and Userlist. The computer field has one name, but the userlist column can vary from 1 user to 8 or more, seperated by semicolons. eg (joe;sally;lou;)
i need to link this table with another table that has a list of computer names and users, but the users in this second table are singular, ie one user per column. is there an easy way to do a WHERE statement to match up the singular user to the string of users?

ex: WHERE table2.user in (table1.userlist) and

or would it be easier to parse out the first list of users into a new table that has each username matched up with a computer

computer=NYcomputer1, userlist=joe;sally;lou;

computer=NYcomputer1, user=joe
computer=NYcomputer1, user=sally
computer=NYcomputer1, user=lou

any help is appreciated! thanks!

Not Just a Number

15586 Posts

Posted - 08/04/2005 :  16:21:49  Show Profile  Reply with Quote
There are lots of articles on SQL team, and there are always the threads to search for answers, but I think you want to read this

EDIT: Who denormalized the data in the first place. Very bad form that.



Hint: Want your questions answered fast? Follow the direction in this link

Edited by - X002548 on 08/04/2005 16:22:37
Go to Top of Page

Starting Member

18 Posts

Posted - 08/04/2005 :  16:26:09  Show Profile  Reply with Quote
we're pulling data from the computers directly via a 3rd party application and dumping it into SQL on a regular basis. Along with a lot of other data, it returns anything with more than one value in a semicolon seperated state.... which is quite possibly the most annoying thing i've run into in a long time, but due to the mass amounts of data we are pulling, this is the most efficient way to pull the data... we just have to get creative in pulling out the information we want.
Go to Top of Page

Starting Member

18 Posts

Posted - 08/04/2005 :  16:47:26  Show Profile  Reply with Quote
ok i'm confused, here is the function i made:

ALTER Function SemiToChar ( @Array varchar(1000))
returns @CharTable table
(CharValue varchar)

declare @separator char(1)
set @separator = ';'

declare @separator_position int
declare @array_value varchar(1000)

set @array = @array + ';'

while patindex('%;%' , @array) <> 0

select @separator_position = patindex('%;%' , @array)
select @array_value = left(@array, @separator_position - 1)

Insert @CharTable
Values (Cast(@array_value as varchar))

select @array = stuff(@array, 1, @separator_position, '')


and now for my select query i'm using:

Declare @TheCSV varchar(100)
set @TheCSV = localadministrators

select computername, LocalAdministrators
from dbo.bigfix_host_data
JOIN requinn.SemiToChar(@TheCSV) CSV
ON CSV.CharValue = localadministrators

but it is telling me i can't use my column name as the variable value.... what am i doing wrong?
Go to Top of Page


United Kingdom
22859 Posts

Posted - 08/05/2005 :  00:41:22  Show Profile  Reply with Quote
It needs

set @TheCSV = 'joe;sally;lou;'

so to process this on every row in the bigfix_host_data table you will have to loop round one-by-one - best to insert the split data into a "wanted" table as you described earlier.

Going forwards you could do that in a trigger on bigfix_host_data whenever a row is inserted, or the LocalAdministrators column is changed, so that it freshens up your proposed "wanted" column

Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000