Q:
Excluding Data Usin NOT IN
I'm trying to export data from a sql table into sql reporter. The information I'm trying to retrieve is information regarding permissions (this is audit stuff). The problem is, there are spaces (and always a different amount of spaces) between the user and the permissions; for example:
Type Account Permissions
----- ---------------------------- --------------
Allow CREATOR OWNER [Full Control]
Allow FM-ACUST-CM01\Administrators [Full Control]
Allow FM-ACUST-CM01\Power Users [RWXD--]
Allow FM-ACUST-CM01\Users [R-X---]
So, the amount of spaces between OWNER and [F... will be different.
My goal is to only pull into the sql report, information where power users is something other then r,w,x & d. Same goes for users, if it is anything other the r and x, I would like the information to be supplied in the report otherwise, I don't need to see it.
Type Account Permissions
----- ---------------------------- --------------
Allow CREATOR OWNER [Full Control]
Allow FM-ACUST-CM01\Administrators [Full Control]
Allow FM-ACUST-CM01\Power Users [RWXD--]
Allow FM-ACUST-CM01\Users [R-X---]
So, the amount of spaces between OWNER and [F... will be different.
My goal is to only pull into the sql report, information where power users is something other then r,w,x & d. Same goes for users, if it is anything other the r and x, I would like the information to be supplied in the report otherwise, I don't need to see it.
ASKED:
Jul 7 2008 1:10 PM GMT
A:
RATE THIS ANSWER
0
Click to Vote:
0
Click to Vote:
- 0
0
I have put the first line into a variable (@c) but this will work if you use column name instead of variable
declare @c varchar(300)
set @c='Allow CREATOR OWNER [Full Control]'
select Left(@c,Len(@c)- CHARINDEX ( '[', @c COLLATE Latin1_General_BIN)) as FullName,
Right(@c,Len(@c)- CHARINDEX ( '[', @c COLLATE Latin1_General_BIN)) as TPermissions;
Gives following results:
Allow CREATOR Full Control]
You can decide if you want to trim the last ] off
