Excluding Data Usin NOT IN

10 pts.
Tags:
SQL Server permissions
SQL Server reporting
SQL statements
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-CM01Administrators [Full Control] Allow FM-ACUST-CM01Power Users [RWXD--] Allow FM-ACUST-CM01Users [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.

Answer Wiki

Thanks. We'll let you know when a new response is added.

Use Charindex to find position of ‘['

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

Discuss This Question:  

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following