Querying delimited column value in SQL Server

Question

I'm wondering why is this code not returning any result

   SELECT A.PermissionGroupID, A.ApplicationID, CONVERT(nvarchar(5),A.PermissionID) AS Permission, A.PermissionName, B.PermissionGroupName, C.ApplicationName 
     FROM Permission AS A 
     JOIN PermissionGroup AS B ON A.PermissionGroupID = B.PermissionGroupID 
LEFT JOIN Application AS C ON A.ApplicationID = C.ApplicationID 
    WHERE (A.Active = 1) 
      AND (CONVERT(nvarchar(2),A.PermissionID) IN (SELECT Permissions 
                                                   FROM UserPermissions 
                                                   WHERE UserID = 1))

But this one is working

   SELECT A.PermissionGroupID, A.ApplicationID, CONVERT(nvarchar(5),A.PermissionID) AS Permission, A.PermissionName, B.PermissionGroupName, C.ApplicationName 
     FROM Permission AS A 
     JOIN PermissionGroup AS B ON A.PermissionGroupID = B.PermissionGroupID 
LEFT JOIN Application AS C ON A.ApplicationID = C.ApplicationID 
    WHERE (A.Active = 1) 
      AND (CONVERT(nvarchar(2), A.PermissionID) IN ('5','6','7','8'))

Here's the content of my Permission table

    Permission
--------------------------------
Permission   Permission    Application    Permission
ID           Group         ID             Name
4         1              1            VISA_APPLICATION_DELETE
5         1              1            VISA_APPLICATION_PRINT
6         4              10           APPLICATION_ADD   
7         4              10           APPLICATION_EDIT
8         4              10           APPLICATION_DELETE
9         4              10           APPLICATION_VIEW

Here's the content of my UserPermissions Table

UserPermissions
--------------------------
UserPermission      UserID    Permissions
ID
2           1         5,6,7,8   -> I tried to change it manually to this format ('5','6','7','8') but to no avail.

Could you please give examples of what is stored in `Permissions` column of `UserPermissions` table (when empty, one value or multiple values)? — van, May 21 '12 at 14:39
Code: The easier it is to read, the more likely others will help. — OMG Ponies, May 21 '12 at 14:47
What do you get when you run `SELECT Permissions FROM UserPermissions WHERE UserID = 1`? Also, why are you casting these (presumably integer) values to text? Do you get expected results with just `A.PermissionID IN (SELECT...)`? — , May 21 '12 at 14:48

score 1 · Answer 1 · answered May 21 '12 at 14:54

1

Why do I suspect that permissions looks like a string "5, 6, 7, 8". I would expect the query to be "where PermisionId in (Select PermissionId from . . . )", rather than (select Permissions).

Assuming this is the case, the following version of the query should fix your problem:

SELECT A.PermissionGroupID, A.ApplicationID, CONVERT(nvarchar(5),A.PermissionID) AS Permission,
       A.PermissionName, B.PermissionGroupName, C.ApplicationName
FROM Permission A JOIN
     PermissionGroup B
     ON A.PermissionGroupID = B.PermissionGroupID LEFT JOIN
     Application AS C
     ON A.ApplicationID = C.ApplicationID cross JOIN
     (SELECT Permissions
      FROM UserPermissions
      WHERE UserID = 1
     ) p1
WHERE (A.Active = 1) AND
      charindex(','+CONVERT(nvarchar(2)+',', A.PermissionID)+',', ','+p1.Permissions+',') > 0

Note I prepend and append the comma so "1" does not match "15". Also, this assumes that there is only one row in UserPermissions per user.

By the way, you should fix your schema so UserPermissions has a separate row for each user and permission, so your original formulation would work.

answered May 21 '12 at 14:54

Gordon Linoff

1,242,037
58
646
786

2

Perhaps you suspect it because the title is *"Querying **delimited column** value in SQL Server"* ? – ypercubeᵀᴹ May 21 '12 at 15:00
1

You need to check your `CHARINDEX` statememt. It will fail. It contains a convert statement like so **CONVERT(nvarchar(2)+ ',', A.PermissionID)**. You are also not considering the first item in the list e.g. with what it appears you were trying to acheive in the list **"1,2,3,4"** you would never match **"1"** because you are looking for **",1,".** – GarethD May 21 '12 at 15:08
Try `(Permissions LIKE CONVERT(NVARCHAR(2), A.PermissionID) + ',%' OR Permissions LIKE '%,' + CONVERT(NVARCHAR(2), A.PermissionID) + ',%')` – GarethD May 21 '12 at 15:10
Hi Gareth, I read in some forum that using like will have performance issues? Is it true? – Sherry Ann Hernandez May 22 '12 at 05:36
Hi Gordon, I remove all the comma from your sample code. Just one doubt, will using charindex affects the performance of my query if let's say I have thousand of rows in userPermissions table and permission table? – Sherry Ann Hernandez May 22 '12 at 05:44

score 0 · Answer 2 · answered May 22 '12 at 06:05

0

From comments I see that you worry about performance. Performance issues are due to the fact that you store a comma separated string of values where you should have one row for each value.
Change table UserPermissions to something like this.

create table UserPermissions
(
  UserID int references Users(UserID),
  PermissionID int references Permission(PremissionID),
  primary key (UserID, PermissionID)
)

And your query should be...

SELECT A.PermissionGroupID,
       A.ApplicationID, 
       A.PermissionID,
       A.PermissionName,
       B.PermissionGroupName,
       C.ApplicationName 
FROM Permission AS A 
  JOIN PermissionGroup AS B 
    ON A.PermissionGroupID = B.PermissionGroupID 
  LEFT JOIN Application AS C 
    ON A.ApplicationID = C.ApplicationID 
WHERE A.Active = 1 AND 
      A.PermissionID IN (SELECT PermissionID 
                         FROM UserPermissions 
                         WHERE UserID = 1)

answered May 22 '12 at 06:05

Mikael Eriksson

136,425
22
210
281

Hi Mikael, I was thinking actually that it will be much easier as the "JOIN" will be lesser. That's why I decided to store it in a comma delimited value. The reason also I make it a delimited value because aside from the individual permission, one of my requirement also is to add a UserGroupPermission – Sherry Ann Hernandez May 22 '12 at 07:31
@SherryAnnHernandez You should create UserGroupPermissions in the same way, as a junction table between UserGroup and Permission. Here is link to some thoughts on why using a comma delimited value is a bad idea. [Is storing a comma separated list in a database column really that bad?](http://stackoverflow.com/a/3653574/569436). – Mikael Eriksson May 22 '12 at 10:04
Storing a comma separated list is not, in itself, always bad. It just becomes a performance nightmare when you want to join to another table or quickly look up a single value (can't use indexes). Don't confuse the proper structure of data in a database with what you want to see as the results of a query. – Gordon Linoff May 22 '12 at 10:37
@Mikael, Yep I can do that but I forgot to mention that the design that my client wants is the functionality to assign several UserGroupPermission to one UserGroupPermission.So I decided that I'll design it the same way wherein the UserGroupPermission is also assigned by getting the UserGroupPermissionID, saving it as a delimited text and assign it to one UserGroupPermissionID. This UserGroupPermissionID can be assigned to one User. – Sherry Ann Hernandez May 22 '12 at 10:56
Another question, for updating a permission, will it be time consuming to check if the specific permission is existing before I add it in a table? Sorry if I have a lot of questions. I'm the only one in our team and have no one to brainstorm it with. :D – Sherry Ann Hernandez May 22 '12 at 11:22
If you mean the `UserPermissions` table then no, it will not. It is impossible to add a permission for a user that already exist in the table because of the primary key. – Mikael Eriksson May 22 '12 at 11:35

Querying delimited column value in SQL Server

2 Answers2