How to match CSV field values in SQL Server 2005

5 pts.
Tags:
CSV
CSV file format
CSV format
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.???

thanks, sanjeev

Answer Wiki

Thanks. We'll let you know when a new response is added.

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.

Discuss This Question:  

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following