Home > IT Answers > SQL Server > How to match CSV field values in SQL Server 2...
Sanjeevgoyal
5 pts.
0
Q:
How to match CSV field values in SQL Server 2005
SQL Server 2005, CSV file format, CSV format, CSV
i have an table in sql-server 2005, in which my one field is storing csv values.
suppose i have two tables; one is roles and another is users;
and multiple role can be assign to one user. so that i have make a fields i.e roles in user table which is keeping the values in csv format.
now my problem is :::
i am displaying roles listing and their is checkbox in first field of that listing like in yahoo.. means i can select multiple item at one time for any action;
at bottom i have three options i.e activate/deactivate/delete.
now i am sending a csv which contains ids and want to performs an action i.e activate/deactivate/delete.
but i want to check if any of role is associated with any user then it should give me a message. so how i will check csv values with csv which is already stored in user table with roles csv field.???

please reply me at geniusskg@gmail.com

thanks,
sanjeev
ASKED: Mar 26 2009  7:18 AM GMT
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
RELATED QUESTIONS
0
Mrdenny
47070 pts.
0
A:
 RATE THIS ANSWER
0
Click to Vote:
  •   0
  •  0
  • AddThis Social Bookmark Button
First of all, I would recommend not to use that csv field, but create a new table to store user-role relations.

But if you are going to keep your current design, you will probably need to use a function to check the csv values. Here’s an example, but I’m not sure what exactly you want to check, so, you might need to make some changes. This function returns 1 if the value sent in @p_value exists in the csv list sent in @p_csv.

At least this should give you an idea.

CREATE FUNCTION YourCheckFunction (@p_csv varchar(100),@p_value varchar(10))
 RETURNS INTEGER
 AS
 BEGIN
 	DECLARE @pos integer = 1
 	SET @pos = CHARINDEX(','+@p_value+',',@p_csv,1)
 	IF @pos <= 0
 	BEGIN
 		SET @pos = CHARINDEX(@p_value+',',@p_csv,1)
 		IF @pos <> 1
 		BEGIN	
 			SET @pos = CHARINDEX(','+@p_value,@p_csv,1)
 			IF @pos + LEN(@p_value) <> LEN(@p_csv)
 				RETURN 0
 		END
 	END
 	RETURN 1
 END


----------

Carlos is correct, you need to redesign your table. Storing values in a CSV string like this is very poor database design and should be corrected.

You should create a table called UserRole which has two columns UserId and RoleId. For each role that the user has you have one row in this table.
Last Answered: Mar 26 2009  8:49 PM GMT by Mrdenny   47070 pts.
Latest Contributors: Carlosdl   29350 pts.
  •   
0
0
RSS - Discussions Help
Discuss This Answer:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _



0