April 1, 2009 at 12:06 am
Hi
I am using two tables tblUsers and tblGroups
tblUsers : *intAutoUserId,strUserName and tblGroups : *intAutoGroupId,strGroupName,+intAutoUserId
in my tblUsers table,
intAutoUserId strUserName
1 Ashok
2 Raja
3 Prajeen
4 Jomet
In my tblGroups table,
intAutoGroupId strGroupName intAutoUserId
1 oilgroup 1,2,3
2 gasgroup 2,3,4
what my doubt is i stored like string intAutoUserId in tblGroups. Using "IN" can we take this string individually for a particular group name??
Otherwise can we split this string and take it? Please tell the suggestion. Its very urgent.
Thanks
April 1, 2009 at 12:26 am
Hi
Have a look to this article for a fast way to split strings:
http://www.sqlservercentral.com/articles/TSQL/62867/
Greets
Flo
April 2, 2009 at 1:59 pm
First, I would not use this table structure.
I would have a users table, a group table, and a GroupUsers table.
Storing all of the user ids in one column is a bad idea. You lose referential integrity as well as forcing yourself to parse the values like you are currently requesting.
To parse the values you can do something like the following. I would change the data model though.
-- Be sure to drop the numbers table if it exists
DROP TABLE dbo.Numbers;
GO
-- Now re-create it and fill it with sequential numbers starting at 1 to 10000 (can up if you need)
SELECT TOP 10000 IDENTITY(INT,1,1) AS Num
INTO dbo.Numbers
FROM master.INFORMATION_SCHEMA.COLUMNS i1
CROSS JOIN master.INFORMATION_SCHEMA.COLUMNS i2
CROSS JOIN master.INFORMATION_SCHEMA.COLUMNS i3;
GO
-- Add a primary key/clustered index to the numbers table
ALTER TABLE dbo.Numbers
ADD CONSTRAINT PK_Numbers PRIMARY KEY CLUSTERED (Num);
GO
--crazxy value table
CREATE TABLE #tblGroups ( intAutoGroupId int IDENTITY(1,1), strGroupName varchar(25) NOT NULL, intAutoUserId varchar(100) NULL )
INSERT INTO #tblGroups VALUES ( 'oilgroup', '1,2,3')
INSERT INTO #tblGroups VALUES ( 'gasgroup', '2,3,4')
--parse the values and select
SELECT
PV.*
FROM ( --parse values
SELECT
G.intAutoGroupId,
SUBSTRING(G.intAutoUserId + ',', N.number, CHARINDEX(',', G.intAutoUserId + ',',N.number) - N.number) as intAutoUserId
FROM #tblGroups G
CROSS JOIN Numbers N
WHERE G.intAutoUserId > ''
AND N.number <= len(G.intAutoUserId) --limit the number of records in the crossjoin for speed
AND SUBSTRING(',' + G.intAutoUserId, n.number, 1) = ',' --we know where a delimeter is
) PV
WHERE PV.intAutoUserId = '2'
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply