SQL parsing question - SQL Server Forums

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

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

SQL parsing question

Author

Topic

becksinthecity
Starting Member

18 Posts

Posted - 2005-08-04 : 15:50:20

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 table2.computer=table1.computer

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

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

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

any help is appreciated! thanks!

X002548
Not Just a Number

15586 Posts

Posted - 2005-08-04 : 16:21:49

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

http://www.sqlteam.com/item.asp?ItemID=11499

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

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

becksinthecity
Starting Member

18 Posts

Posted - 2005-08-04 : 16:26:09

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.

becksinthecity
Starting Member

18 Posts

Posted - 2005-08-04 : 16:47:26

ok i'm confused, here is the function i made:

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

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

declare @separator_position int
declare @array_value varchar(1000)

set @array = @array + ';'

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

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, '')
end

return
end

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?

Kristen
Test

22859 Posts

Posted - 2005-08-05 : 00:41:22

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

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources