CODEWITHSUNDEEP
sqlms-accesssharepoint
Varun Menon
Varun Menon

Reputation: 3

Select rows where user id column has only specified string

I have extracted a spreadsheet of all users with Full Control access on our SharePoint farm using PowerShell.

Now, from that list I am trying get a list of sites where only the SP admins are exclusive owners. I loaded the extracted spreadsheet in Access and am writing queries to generate various reports.

All user IDs for one SP site are stored in a cell in a column called UserID and they are separated by semi-colons: User1; User2; and so on.

SiteName Url                UserID                
-------- ---------------    ----------------             
Site1    https://test.com   User1;User2;User3;
Site2    https://test2.com  User1;User3;User5;...;User50;

I have a table with a UserID column.

Ex: User1, 2 and 3 are the SP admins

Row 1 has UserID column values: "User1; User2; User3"
Row 2: "User2; User3;"
Row 3: "User1; User4; .... ;User50;"
Row 4: "User1; User2; User4;... ;User25;"
Row 5: "User3;"

I am trying to design a query where only rows containing either User1, User2 or User3 exist in that cell. Which basically means only Rows 1, 2 and 5 return in the results. Row3 and Row4 should not return in the result because it has users other than User1, 2 and 3 in their cells.

Using LIKE doesn't help this because it returns every row where it finds one of these users. I cannot use NOT LIKE to exclude the remaining users because a lot of rows have over 30 users listed in a cell having full control access.

Upvotes: 0

Views: 202

Answers (2)

forpas
forpas

Reputation: 164099

The spaces (if they are there) inside the UserID column make the string manipulation of the column more complex.
The idea is to replace 'User1', 'User2' and 'User3' with an empty string and then remove all semicolons and all spaces.
If what is left has length = 0 then this row contains only 'User1' or 'User2' or 'User3' or combinations of them:

select * from tablename
where
len(replace(replace(replace(replace(replace(userid, 'User1', ''), 'User2', ''), 'User3', ''), ';',''), ' ', '')) = 0

Upvotes: 1

Lee Mac
Lee Mac

Reputation: 16015

If you are able to acquire the set of all UserIDs which are not SP admins, then you could use a query such as the following to acquire the rows you require:

select t1.* 
from YourTable t1 left join NotSPAdmins t2 on t1.UserID like '*' & t2.UserID & '*'
where t2.UserID is null

Upvotes: 0

Related Questions