Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
olta
Starting Member
1 Post |
 Posted - 2009-06-02 : 05:58:20
|
| Hello, I have two tables : Roles and Users. In the Roles table I have inserted some roles. In user table I have inserted new Users and they respective roles in one field. All the roles in the field are divided by (,) in the users table.I want to select a user and see his roles. For example : username rolesjohn 1,2,6,7The numbers in the roles field in users table are the respective id_roles in the roles table. I want to access their names and display something like thisusername rolesjohn save edit delete .....Any Idea on how this is performed ???Thankyou in advance |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
|
|
bklr
Master Smack Fu Yak Hacker
1693 Posts |
Posted - 2009-06-02 : 08:34:06
|
| try like this too..declare @str table(username varchar(32),roles varchar(128))insert into @str select 'john','1,2,6,7'select username, rolenamefrom (SELECT username, replace(SUBSTRING(s.roles,charindex(',',s.roles,v.number),abs(charindex(',',s.roles,charindex(',',s.roles,v.number)+1)-charindex(',',s.roles,v.number))),',','')as roleidFROM @str AS sINNER JOIN master..spt_values AS v ON v.Type = 'P' and v.number > 0 and v.number <= len(s.roles)WHERE substring(',' + s.roles, v.number, 1) = ',')sinner join roles r on s.roleid = r.respective id |
 |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2009-06-02 : 08:46:54
|
| Also you should read about NormalisationMadhivananFailing to plan is Planning to fail |
|
|
|
|
|
|
|
|
Split field values