Using a string of numbers in SQL

[mts176]

I am filling in an ID table it has user_id and rights_id.

each user has multiple rights but they are not in order.
User 1 with an id of 2 has the rights 1,3,5,8,16,20

Is there a way to make a variable = to this string of numbers then pull it apart to put them into the table like this

U_ID R_ID
1 1
1 3
1 5
. .
. .
. .
1 20

 

[vechalapu]

Please try these steps to achieve what you want:

1.Declare a character variable which can hold all the R_IDs for any given U_ID.

2. Declare another varaible of integer to store R_ID as you loop through the string holding all the R_IDs. Loop through the string, compare each character with comma using substring function, if it is comma, move the next character. If the previous character and next characters are integers, treat it is one R_ID till you reach the end of the string.

Hope this helps.