Bitwise AND in Where clause?

Is there any way to use Bitwise AND in a WHERE clause?
EG:
SELECT * FROM Directory WHERE DirectoryFeatured & 4
This just gives me an incorrect syntax error? DirectoryFeatured is an int
field.
Regards,
ChrisNot sure how you store the data, but possibly this is what you are looking f
or:
SELECT * FROM Directory WHERE DirectoryFeatured & 4 = 4
Consider normalizing the table and split each attribute to one column (or an
other table, depending
on the data etc).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Chris Ashley" <chris.ashley@.SPAMblueyonder.co.uk> wrote in message news:42e64dc4.0@.entanet
..
> Is there any way to use Bitwise AND in a WHERE clause?
> EG:
> SELECT * FROM Directory WHERE DirectoryFeatured & 4
> This just gives me an incorrect syntax error? DirectoryFeatured is an int
field.
> Regards,
> Chris
>|||In SQL, WHERE clauses return one of TRUE, FALSE or UNKNOWN.
An t-SQL bitwise expression DirectoryFeatured & 4 returns an INT value which
is incompatible with the above set of values. A logical/bitwise AND between
the two expressions, taking each corresponding bit for both expressions.
Thus your expression should take the form of:
SELECT * FROM tbl WHERE col & 4 = <some val>
In general, there is not many instances when one needs to use bitwise
operators in commerical SQL applications. Though it occasionally finds its
place, quite often poorly designed schema is a prime reason for people
resorting to complex bitwise logic on integer typed columns.
Anith|||The statement is not complete. Try (untested):
SELECT * FROM Directory WHERE DirectoryFeatured & 4 = 4
The bitwise operator will return a value (not True or False); you have to
test for the expected value.
As an aside, this is a relatively inefficient thing to do, and it would
probably be best to create an encoding scheme that did not rely on low-level
bit manipulation.
"Chris Ashley" <chris.ashley@.SPAMblueyonder.co.uk> wrote in message
news:42e64dc4.0@.entanet...
> Is there any way to use Bitwise AND in a WHERE clause?
> EG:
> SELECT * FROM Directory WHERE DirectoryFeatured & 4
> This just gives me an incorrect syntax error? DirectoryFeatured is an int
> field.
> Regards,
> Chris
>

Bit of a beginner question on SQL-XML

Hello there, I have a very basic question but I'd appreciate some help. My query results in SQL QUERY ANALYSER with a FOR XML clause are not yielding correctly formatted results. I am expecting to see XML elements returned on individual lines. Instead my results are returned all concatenated onto one line and truncated after 256 characters (the default for a column in my results tab). I can increase the number of characters but that does not improve the formatting. Large result sets produce multiple rows but the next row does not start where the previous one finished, so the correct results cannot be reconstructed even by manually adding carriage returns

e.g.

My query

SELECT Code, ClassName FROM CLASSMAPPING
FOR XML RAW

returns the results like this, it's a single line of text word-wrapped and truncated after 256 characters:

<row Code="AUS" ClassName="Australia"/><row Code="CAN" ClassName="Canada"/><row Code="GBR" ClassName="UK"/><row Code="IRL" ClassName="Ireland"/><row Code="NZL" ClassName="New Zealand"/><row Code="PAR" ClassName="Partner schools"/><row Code="USA" ClassName=

But I surely I should really see results laid out like this:

<row Code="AUS" ClassName="Australia"/>
<row Code="CAN" ClassName="Canada"/>
<row Code="GBR" ClassName="UK"/>
<row Code="IRL" ClassName="Ireland"/>
<row Code="NZL" ClassName="New Zealand"/>
<row Code="PAR" ClassName="Partner schools"/>
<row Code="USA" ClassName="USA"/>

I can't find any information about a switch or trigger to make this work but all the literature I've read indicates it should work. Has anyone seen this before? Can you help?

I have SQL server 2000 with W2K3 server.

I don't think FOR XML does any attempt to format the XML it generates for human reading so you have to live with lack of line breaks and indentation. If you look at the result in an XML editor then it should provide you an option to pretty print the XML.

|||

I am not an expert but I am trying to learn like you; maybe something like this?

Code Snippet

declare @.xml xml
set @.xml = '<row Code="AUS" ClassName="Australia"/><row Code="CAN" ClassName="Canada"/><row Code="GBR" ClassName="UK"/><row Code="IRL" ClassName="Ireland"/><row Code="NZL" ClassName="New Zealand"/><row Code="PAR" ClassName="Partner schools"/>'

select x.t.query('.') as Results from @.xml.nodes('/row') x(t)

/*
Results
-
<row Code="AUS" ClassName="Australia" />
<row Code="CAN" ClassName="Canada" />
<row Code="GBR" ClassName="UK" />
<row Code="IRL" ClassName="Ireland" />
<row Code="NZL" ClassName="New Zealand" />
<row Code="PAR" ClassName="Partner schools" />
*/

|||Are you sure about this? Many queries on an average database will generate far more than the maximum 8192 characters for a single line. The results out of QUERY ANALYSER will be pretty much unusable in almost every case. That doesn't seem very sensible. Surely at least each <row /> element (FOR XML RAW) would drop into a separate results row?
|||I have not done much with SQL Server 2000 but with SQL Server 2005 the XML generated with FOR XML is all on one line. However SQL Server Management Studio or Management Studio Express has a built-in XML editor pane where it pretty prints and colorizes the XML for human viewing.|||OK, thanks for confirming this.
|||

I have, I think, the same problem; given N rows, return a recordset with N xml documents.

The 'FOR XML EXPLICIT' looks like the way to go, except that I can't break up the result into distinct rows.

|||

Could be that this solution will help;

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1702105&SiteID=1

Bit of a beginner question on SQL-XML

Hello there, I have a very basic question but I'd appreciate some help. My query results in SQL QUERY ANALYSER with a FOR XML clause are not yielding correctly formatted results. I am expecting to see XML elements returned on individual lines. Instead my results are returned all concatenated onto one line and truncated after 256 characters (the default for a column in my results tab). I can increase the number of characters but that does not improve the formatting. Large result sets produce multiple rows but the next row does not start where the previous one finished, so the correct results cannot be reconstructed even by manually adding carriage returns

e.g.

My query

SELECT Code, ClassName FROM CLASSMAPPING
FOR XML RAW

returns the results like this, it's a single line of text word-wrapped and truncated after 256 characters:

<row Code="AUS" ClassName="Australia"/><row Code="CAN" ClassName="Canada"/><row Code="GBR" ClassName="UK"/><row Code="IRL" ClassName="Ireland"/><row Code="NZL" ClassName="New Zealand"/><row Code="PAR" ClassName="Partner schools"/><row Code="USA" ClassName=

But I surely I should really see results laid out like this:

<row Code="AUS" ClassName="Australia"/>
<row Code="CAN" ClassName="Canada"/>
<row Code="GBR" ClassName="UK"/>
<row Code="IRL" ClassName="Ireland"/>
<row Code="NZL" ClassName="New Zealand"/>
<row Code="PAR" ClassName="Partner schools"/>
<row Code="USA" ClassName="USA"/>

I can't find any information about a switch or trigger to make this work but all the literature I've read indicates it should work. Has anyone seen this before? Can you help?

I have SQL server 2000 with W2K3 server.

I don't think FOR XML does any attempt to format the XML it generates for human reading so you have to live with lack of line breaks and indentation. If you look at the result in an XML editor then it should provide you an option to pretty print the XML.

|||

I am not an expert but I am trying to learn like you; maybe something like this?

Code Snippet

declare @.xml xml
set @.xml = '<row Code="AUS" ClassName="Australia"/><row Code="CAN" ClassName="Canada"/><row Code="GBR" ClassName="UK"/><row Code="IRL" ClassName="Ireland"/><row Code="NZL" ClassName="New Zealand"/><row Code="PAR" ClassName="Partner schools"/>'

select x.t.query('.') as Results from @.xml.nodes('/row') x(t)

/*
Results
-
<row Code="AUS" ClassName="Australia" />
<row Code="CAN" ClassName="Canada" />
<row Code="GBR" ClassName="UK" />
<row Code="IRL" ClassName="Ireland" />
<row Code="NZL" ClassName="New Zealand" />
<row Code="PAR" ClassName="Partner schools" />
*/

|||Are you sure about this? Many queries on an average database will generate far more than the maximum 8192 characters for a single line. The results out of QUERY ANALYSER will be pretty much unusable in almost every case. That doesn't seem very sensible. Surely at least each <row /> element (FOR XML RAW) would drop into a separate results row?
|||I have not done much with SQL Server 2000 but with SQL Server 2005 the XML generated with FOR XML is all on one line. However SQL Server Management Studio or Management Studio Express has a built-in XML editor pane where it pretty prints and colorizes the XML for human viewing.|||OK, thanks for confirming this.
|||

I have, I think, the same problem; given N rows, return a recordset with N xml documents.

The 'FOR XML EXPLICIT' looks like the way to go, except that I can't break up the result into distinct rows.

|||

Could be that this solution will help;

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1702105&SiteID=1