January 30, 2018 at 8:45 pm
###Business Problem: I have a list of users and attributes that apply to the users (stored in user table). The complete list of user attributes is called userList. This list is delimited by semi-colons.
userList Ex: reader-comprehension-level;reader-req-ind;read-max-level;reader-type;
I have another list called roleList (stored in role table). The roleList groups all possible user attributes and gives the grouping a role name.
readerList Ex: reader-comprehension-level;reader-req-ind;read-max-level;reader-type; expert-reader-level;
It is possible for a readerList to have more attributes than a user has for their userList.
User Table:
User_ID | UserList
1 | reader-comprehension-level;reader-req-ind;read-max-level;reader-type;struggle-reader
Role Table:
Role_Name | RoleList
Role_1 | reader-comprehension-level;reader-req-ind;read-max-level;reader-type; expert-reader-level; really-good-reader;struggle-reader
###Request:
I must associate a user id to a role. The attributes found in the userList can appear in any order, but are delimited by semicolon.
Initially, I tried a left outer join between the User and Role table (joining on the UserList and RoleList). This would only associate a role with a user if there was an exact match.
I need to compare each part of the delimited string from the UserList field against the RoleList string. While the section of the UserList (@value) is still found in the RoleList string, the code can proceed adding to my counter @countMatches. However, if a nonMatch is found, at that point in time, i would like to split the userList string at the position where the non-Match occurred. A record should also be inserted into a table associating the user with the role that contained all the attributes upto the point where the non-match occurred.
Then, the
The userList will be updated to just contain the attributes that did not fit into a role starting with the attribute where the non-Match occurred.
This is the code I have so far...need to fill in the rest of the logic in the center:
Declare @userList varchar(8000) --List of user attributes
Declare @roleList varchar(8000) -- list of role attributes
Declare @pos INT --position in UserList
Declare @pos2 INT
Declare @len INT
Declare @len2 INT
Declare @value --value of current user attribute
Declare @countMatches INT
Declare @totalEntitlements INT
Declare @character char(1)
Declare @matchFound bit
SET @userList = ‘reader-comprehension-level;reader-req-ind;read-max-level;reader-type;struggle-reader’
SET @roleList = ‘reader-comprehension-level;reader-req-ind;read-max-level;reader-type; expert-reader-level; really-good-reader;struggle-reader’
Set @character = ‘;’
Set @totalAttributes = (len(@userList) – len(replace(@valueList, @character, ‘’))) / len(@character)
/*given that each attribute is separate by semicolon, counting the occurrence of semicolons should return the number of attributes for a userList*/
While charindex(‘;’, @userList, @pos+1) -@pos
BEGIN
Set @len = charindex(‘;’, @valueList, @pos+1) - @pos
Set @value = substring(@userList, @pos, @len)
/*
--Code Section That Needs Work: The idea is to take eah @value (each user attribute) and --compare it against the roleList; if pattern is found in the role list, then increment counter @countMatches
If charindex(@value, @roleList, @pos2) > 0
@countMatches = @countMatches + 1
Begin
Else
--When non match is found, break string at current position and associate role name with user id (make insert a record with user_id and role_name into a table)
@userList = right(@userList, len(@userList) – charindex(@value, @userList, positionWhereNonMatchValueWasEncountered) –attempt to break string
--After string is broken, the remainder of the attributes in @userList must now be compared against the list of roleLists.
*/
END
Any hints on how to accomplish this task ?
Other sources referenced:
Any ideas on how to accomplish this task ?
Other source referenced: https://techforpassion.blogspot.com/2013/03/how-to-split-comma-separated-string-and.html#comment-form
Counting occurences of String comes from ITPro Today article
January 30, 2018 at 9:38 pm
Could you post a CREATE TABLE script and an INSERT script to populate it?
I think you can use Jeff Moden's DelimitedSplit8K function to break the individual attributes into individual pieces. Then you can query it. The problem with storing the data as a delimited list is that you can't really index it in a meaningful or useful way, because it will only effectively index the first value in the string.
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply