Q:
How to match CSV field values in SQL Server 2005
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
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
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
A:
RATE THIS ANSWER
0
Click to Vote:
0
Click to Vote:
- 0
0
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.
