SQL Command Help

[Kayot]

Is there an SQL command (Select type) that will return a column as a bitmask? Is so, is there a way to filter the list based on one bit inside the bitmask?


Like list all bitmask were ???????1???, ? can be either 0 and 1.

And one were 00000001000 is the bitmask?

[samandhi]

Try this:

Originally Posted by The MySQL manual

3.3.4.7 Pattern Matching

MySQL provides standard SQL pattern matching as well as a form of pattern matching based on extended regular expressions similar to those used by Unix utilities such as vi, grep, and sed.

SQL pattern matching allows you to use `_' to match any single character and `%' to match an arbitrary number of characters (including zero characters). In MySQL, SQL patterns are case-insensitive by default. Some examples are shown here. Note that you do not use = or <> when you use SQL patterns; use the LIKE or NOT LIKE comparison operators instead.

To find names beginning with `b':

mysql> SELECT * FROM pet WHERE name LIKE "b%";
+--------+--------+---------+------+------------+------------+
| name | owner | species | sex | birth | death |
+--------+--------+---------+------+------------+------------+
| Buffy | Harold | dog | f | 1989-05-13 | NULL |
| Bowser | Diane | dog | m | 1989-08-31 | 1995-07-29 |
+--------+--------+---------+------+------------+------------+

To find names ending with `fy':

mysql> SELECT * FROM pet WHERE name LIKE "%fy";
+--------+--------+---------+------+------------+-------+
| name | owner | species | sex | birth | death |
+--------+--------+---------+------+------------+-------+
| Fluffy | Harold | cat | f | 1993-02-04 | NULL |
| Buffy | Harold | dog | f | 1989-05-13 | NULL |
+--------+--------+---------+------+------------+-------+

To find names containing a `w':

mysql> SELECT * FROM pet WHERE name LIKE "%w%";
+----------+-------+---------+------+------------+------------+
| name | owner | species | sex | birth | death |
+----------+-------+---------+------+------------+------------+
| Claws | Gwen | cat | m | 1994-03-17 | NULL |
| Bowser | Diane | dog | m | 1989-08-31 | 1995-07-29 |
| Whistler | Gwen | bird | NULL | 1997-12-09 | NULL |
+----------+-------+---------+------+------------+------------+

To find names containing exactly five characters, use fives instances of the `_' pattern character:

mysql> SELECT * FROM pet WHERE name LIKE "_____";
+-------+--------+---------+------+------------+-------+
| name | owner | species | sex | birth | death |
+-------+--------+---------+------+------------+-------+
| Claws | Gwen | cat | m | 1994-03-17 | NULL |
| Buffy | Harold | dog | f | 1989-05-13 | NULL |
+-------+--------+---------+------+------------+-------+

The other type of pattern matching provided by MySQL uses extended regular expressions. When you test for a match for this type of pattern, use the REGEXP and NOT REGEXP operators (or RLIKE and NOT RLIKE, which are synonyms).

Some characteristics of extended regular expressions are:

`.' matches any single character.
A character class `[...]' matches any character within the brackets. For example, `[abc]' matches `a', `b', or `c'. To name a range of characters, use a dash. `[a-z]' matches any letter, whereas `[0-9]' matches any digit.
`*' matches zero or more instances of the thing preceding it. For example, `x*' matches any number of `x' characters, `[0-9]*' matches any number of digits, and `.*' matches any number of anything.
A REGEXP pattern match succeed if the pattern matches anywhere in the value being tested. (This differs from a LIKE pattern match, which succeeds only if the pattern matches the entire value.)
To anchor a pattern so that it must match the beginning or end of the value being tested, use `^' at the beginning or `$' at the end of the pattern.
To demonstrate how extended regular expressions work, the LIKE queries shown previously are rewritten here to use REGEXP.

To find names beginning with `b', use `^' to match the beginning of the name:

mysql> SELECT * FROM pet WHERE name REGEXP "^b";
+--------+--------+---------+------+------------+------------+
| name | owner | species | sex | birth | death |
+--------+--------+---------+------+------------+------------+
| Buffy | Harold | dog | f | 1989-05-13 | NULL |
| Bowser | Diane | dog | m | 1989-08-31 | 1995-07-29 |
+--------+--------+---------+------+------------+------------+

especially this line?????:

To find names containing exactly five characters, use fives instances of the `_' pattern character:

mysql> SELECT * FROM pet WHERE name LIKE "_____";
+-------+--------+---------+------+------------+-------+
| name | owner | species | sex | birth | death |
+-------+--------+---------+------+------------+-------+
| Claws | Gwen | cat | m | 1994-03-17 | NULL |
| Buffy | Harold | dog | f | 1989-05-13 | NULL |
+-------+--------+---------+------+------------+-------+

Hope this is something like what you were looking for.

[Kayot]

Thats part of what I needed. Now is there a way to get MySQL to convert its column from say 13 to 00000001111.

[samandhi]

hmmmm maybe this?

You can find the following bitmask definitions in `m_ctype.h':

#define _U 01 /* Uppercase */
#define _L 02 /* Lowercase */
#define _N 04 /* Numeral (digit) */
#define _S 010 /* Spacing character */
#define _P 020 /* Punctuation */
#define _C 040 /* Control character */
#define _B 0100 /* Blank */
#define _X 0200 /* heXadecimal digit */

The ctype[] entry for each character should be the union of the applicable bitmask values that describe the character. For example, 'A' is an uppercase character (_U) as well as a hexadecimal digit (_X), so ctype['A'+1] should contain the value:

_U + _X = 01 + 0200 = 0201

Closest I could find.. No binary that I could see... This doc is in the \doc folder in your MySQL directory....

edit:This might be useful to you check it out:

Binary values such as 0xFFDF now are assumed to be strings instead of numbers. This fixes some problems with character sets where it's convenient to input a string as a binary value. With this change, you should use CAST() if you want to compare binary values numerically as integers:
mysql> SELECT CAST(0xFEFF AS UNSIGNED INTEGER) < CAST(0xFF AS UNSIGNED INTEGER);
-> 0

If you don't use CAST(), a lexical string comparison will be done:
mysql> SELECT 0xFEFF < 0xFF;
-> 1

Using binary items in a numeric context or comparing them using the = operator should work as before. (The --new option can be used from 4.0.13 on to make a 4.0 server behave as 4.1 in this respect.)
For functions that produce a DATE, DATETIME, or TIME value, the result returned to the client now is fixed up to have a temporal type. For example, in MySQL 4.1, you get this result:
mysql> SELECT CAST("2001-1-1" as DATETIME);
-> '2001-01-01 00:00:00'

In MySQL 4.0, the result is different:
mysql> SELECT CAST("2001-1-1" as DATETIME);
-> '2001-01-01'

DEFAULT values no longer can be specified for AUTO_INCREMENT columns. (In 4.0, a DEFAULT value is silently ignored; in 4.1, an error occurs).
LIMIT no longer accept negative arguments. Use 18446744073709551615 instead of -1.
SERIALIZE is no longer a valid option value for the sql_mode variable. You should use SET TRANSACTION ISOLATION LEVEL SERIALIZABLE instead. SERIALIZE is no longer valid for the --sql-mode option for mysqld, either. Use --transaction-isolation=SERIALIZABLE instead.
All tables and string columns now have a character set. See section 9 National Character Sets and Unicode. Character set information is displayed by SHOW CREATE TABLE and mysqldump. (MySQL versions 4.0.6 and above can read the new dump files; older versions cannot.)
The table definition format used in `.frm' files has changed slightly in 4.1. MySQL 4.0 versions from 4.0.11 on can read the new `.frm' format directly, but older versions cannot. If you need to move tables from 4.1 to a version earlier than 4.0.11, you should use mysqldump. See section 4.9.7 mysqldump, Dumping Table Structure and Data.
If you are running multiple servers on the same Windows machine, you should use a different --shared_memory_base_name option on all machines.
The interface to aggregated UDF functions has changed a bit. You must now declare a xxx_clear() function for each aggregate function XXX().

[ndnet]

SQL supports bitwise operators, such as & | ^ ~ etc.

If you have a set of numbers and you want to match them via bitmasks, it's easy to use the bitwise operators to do so.

Example table "bittest" with column "mask" that has some ints: (btw trying to format these sucks)

     +------+
     | mask |
     +------+
     | 5 |
     | 2 |
     | 3 |
     | 8 |
     | 4 |
     +------+

Now you have a bitmask 0100 which is the number 4. You want all numbers that have the bit set to 1.

mysql> SELECT * FROM `bittest` WHERE `mask` & 4;
     +------+
     | mask |
     +------+
     | 5 | < -- 0101
     | 4 | < -- 0100
     +------+

Now with the same mask 0100, you want all numbers where that bit is 0.

mysql> SELECT * FROM `bittest` WHERE NOT `mask` & 4;
     +------+
     | mask |
     +------+
     | 2 | < -- 0010
     | 3 | < -- 0011
     | 8 | < -- 1000
     +------+

And so forth~

[Kayot]

I get it, so to find item with class warrior bit set; since warrior is 1 I set it up like so:

Select classes From items where classes & 1;

And of course if I only want Warrior I just make it generic like so:

Select classes From items where classes = 1;

^-^ This makes things a hell of a lot easier. I was having it post process all the entry's which required a ton of leg work on the program. Now I can ease the processing that was needed. Goodbye 800ish lines, hello simple string insert.

Now to save this page onto my laptop so I can redesign that part of the program (No net access)

P.S. What do the other operators do? I'll probably find out, but short-cutting knowledge is always fun.

[samandhi]

BINARY Cast a string to a binary string
BIT_AND() Return bitwise and
BIT_COUNT() Return the number of bits that are set
BIT_LENGTH() Return length of argument in bits
BIT_OR() Return bitwise or
BIT_XOR() Return bitwise xor
& Bitwise AND
| Bitwise OR
^ Bitwise XOR
/ Division operator

The rest of them are here

[GeorgeS]

Thanks for the mask & nn reference. Never used it before, and just tried it for the items table and it works well. Wish I knew that before


GeorgeS