What Does "&" Means in This SQL Where Clause

What does & means in this sql where clause?

It's called a bitmask. It is used in situations where the individual bits in a number have different meanings, as opposed to a number just meaning the number itself (for instance, if you save your age to the database).

When you imagine any number in it's binary form and want to test if a certain bit in the number is set, you test it by using the binary AND operator with the number and the bit you want to test, like this:

if (number & 16 == 16)

In binary, this means the following (assuming, your number is 25):

if (00011001 & 00010000 == 00010000)

Here you can see, that the digits at the bit 5 (counted from the bottom up) are both 1, therefor the resulting number has a 1 at that bit. As there are no other 1s, the resulting number is 16 exactly when both numbers have a 1 at this position.

I would like to add: It's usually bad practice to encode different meanings into one database field. It's difficult to index and retrieve by an index, and depending on your DBMS might even be completely unindexed.

Ampersand (&) operator in a SQL Server WHERE Clause

& is the bitwise logical and operator - It performs the operation on 2 integer values.

WHERE (sc.Attributes & 1) = 0 

The above code checks to see if sc.Attributes is an even number. Which is the same as saying that the first bit is not set.

Because of the name of the column though: "Attributes", then the "1" value is probably just some flag that has some external meaning.

It is common to use 1 binary digit for each flag stored in a number for attributes. So to test for the first bit you use sc.Attributes&1, to test for the second you use sc.Attributes&2, to test for the third you use sc.Attributes&4, to test for the fourth you use sc.Attributes&8, ...

The = 0 part is testing to see if the first bit is NOT set.

Some binary examples: (== to show the result of the operation)

//Check if the first bit is set, same as sc.Attributes&1
11111111 & 00000001 == 1
11111110 & 00000001 == 0
00000001 & 00000001 == 1

//Check if the third bit is set, same as sc.Attributes&4
11111111 & 00000100 == 1
11111011 & 00000100 == 0
00000100 & 00000100 == 1

The difference between 'AND' and '&&' in SQL

For mySQL: The manual is not saying it explicitly, but they are listed as identical:

AND, &&

Logical AND. Evaluates to 1 if all operands are nonzero and not NULL, to 0 if one or more operands are 0, otherwise NULL is returned.

The operator precedence page also makes no distiction.

Is there a combination of LIKE and IN in SQL?

There is no combination of LIKE & IN in SQL, much less in TSQL (SQL Server) or PLSQL (Oracle). Part of the reason for that is because Full Text Search (FTS) is the recommended alternative.

Both Oracle and SQL Server FTS implementations support the CONTAINS keyword, but the syntax is still slightly different:

Oracle:

WHERE CONTAINS(t.something, 'bla OR foo OR batz', 1) > 0

SQL Server:

WHERE CONTAINS(t.something, '"bla*" OR "foo*" OR "batz*"')

The column you are querying must be full-text indexed.

Reference:

• Building Full-Text Search Applications with Oracle Text
• Understanding SQL Server Full-Text

Escaping ampersand character in SQL string

Instead of

node_name = 'Geometric Vectors \& Matrices'

use

node_name = 'Geometric Vectors ' || chr(38) || ' Matrices' 

38 is the ascii code for ampersand, and in this form it will be interpreted as a string, nothing else. I tried it and it worked.

Another way could be using LIKE and an underline instead the '&' character:

node_name LIKE 'Geometric Vectors _ Matrices' 

The chance that you'll find some other record too, which is different in only this one character, is quite low.

SQL 'LIKE' query using '%' where the search criteria contains '%'

If you want a % symbol in search_criteria to be treated as a literal character rather than as a wildcard, escape it to [%]

... where name like '%' + replace(search_criteria, '%', '[%]') + '%'

SQL what does & in where clause mean?

The ampersand "&" is used for concatenation.

Before

"SELECT * FROM dataTable WHERE Datee Between '#" & textStartDate.Text & "# AND #" & textEndDate.Text & "#'"  

After

"SELECT * FROM dataTable WHERE Datee Between '#" & textStartDate.Text & "#' AND  '#" & textEndDate.Text & "#'" 

The between statement was missing "'" quotes to enclose the date values.

Edit

How to add a wildcard to a non-date sql query. This code has wild cards on both side of the variable.

...WHERE SomeField like  '*" & textWeAreSearchingFor & "*'"

Here are other examples on how to query date datetype.

...WHERE Datee >= '#" & textStartDate.Text & "#'"

OR

...WHERE Datee < '#" & textStartDate.Text & "#'"

---

Related Topics