Select missing rows using SQL LIKE

Question

I have a SQL table like the following

RoleId      Roles
------------------------------------------
  1         Administrators Viewers Users Managers 
  2         Administrators Viewers Managers 
  3         Administrators
  4         Viewers

I need to get the RoleId's based on the roles. I have each role as a string.

So when I use the following query

SELECT * 
FROM Roles 
WHERE Roles like '%Administrators Viewers Managers%'

I get only the first two rows

RoleId      Roles
--------------------------------------------------
  1         Administrators Viewers Users Managers 
  2         Administrators Viewers Managers

But I need it to fetch the remaining rows too since it contains Administrators and Viewers. I am actually using an Stored procedure to supply the roles as a parameter. So it is not always the same. Its dynamic. I cant use OR. I need a solution that will work for any parameter.

Is there a combination between IN and LIKE clause ?

Or how can I proceed

hmmm this is an interested method, but doesnt seem like a good long-term solution. i think ideally you would have 2 tables which would allow you to be more flexible later... ie. Roles, and Permissions... ie. ROLES TABLE: roleID 1=Admin, roleID 2=Viewer PERMISSIONS TABLE: roleID=1, view=yes, admin=yes, edit=yes; roleId=2, view=yes, admin=no, edit=no. Then you would be able to do more complex queries. — timh
– timh, Commented Nov 25, 2014 at 10:18
The script provided above would not bring back record 'Administrators Viewers Users Managers' as 'Users' is not specified in your LIKE clause. If your parameter is dynamic, I would suggest DSQL. Declare a parameter with writes out your query and execute. I am not sure how you are going to break your parameter up into 3 (Administrator / Viewers / Managers) variables though. — PKirby
– PKirby, Commented Nov 25, 2014 at 10:41
I think you need to do some research on relational databases. Your approach is wrong so unless you have a good reason to do it like that then you need to rethink it. — Lee Willis
– Lee Willis, Commented Nov 25, 2014 at 10:57

Tim Schmelter · Accepted Answer · 2014-11-25 10:17:01Z

2

You can use OR:

SELECT * 
FROM Roles 
WHERE Roles like '%Administrators%'
OR    Roles like '%Viewers%'

answered Nov 25, 2014 at 10:17

Tim Schmelter

462k79 gold badges719 silver badges980 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

wickjon Over a year ago

I am actually using an Stored procedure to supply the roles as a parameter. So it is not always the same. Its dynamic.

Tim Schmelter Over a year ago

@Targarian: so what type is the parameter? I would normalize your table. Create another table to store the "Sub-Roles" which belong to a role.

Rahul Sharma · Accepted Answer · 2014-11-25 10:18:08Z

0

SELECT *   FROM Roles 
WHERE Roles like '%Administrators%'  OR    Roles like '%Viewers%' 
OR Roles like '%Administrators Viewers%'

answered Nov 25, 2014 at 10:18

Rahul Sharma

4533 silver badges11 bronze badges

2 Comments

Max Over a year ago

Could you add some additional information along with your answer?

Mike Over a year ago

Isn't the third LIKE completely redundant due to the first two?

Rajesh · Accepted Answer · 2014-11-25 10:50:10Z

0

If you dynamically pass the roles as a parameter use like the below Query for

SQL Server 2000 and above

SELECT * 
FROM Roles 
WHERE Roles like '%'+@Roles+'%'

SQL Server 2008 and above

SELECT * 
FROM Roles 
WHERE Contains(Roles,@Roles)

but the above query will produce the result only based on the roles you have passed

edited Nov 25, 2014 at 10:50

answered Nov 25, 2014 at 10:33

Rajesh

1,6305 gold badges36 silver badges63 bronze badges

Collectives™ on Stack Overflow

Select missing rows using SQL LIKE

3 Answers 3

2 Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related