[Level 3] rw_ether_atob, rw_ether_btoa stored function in MySQL. 

delimiter //

drop function if exists rw_ether_atob//
create function rw_ether_atob(sAscii char(17))
returns bit(48)
deterministic
begin
  declare bReturn bit(48);
  ##set bReturn=conv(replace(sAscii,':',''),16,2); ## string output not bit
  ##set bReturn=conv(replace(sAscii,':',''),16,10); ## not work
  ##set bReturn=cast(replace(sAscii,':','') as bit(48)); ## syntax error
  ##set bReturn=bin(replace(sAscii,':','')); ## syntax error
  set bReturn=unhex(replace(sAscii,':',''));
  return bReturn;
end//

drop function if exists rw_ether_btoa//
create function rw_ether_btoa(sBit bit(48))
returns char(17)
deterministic
begin
  declare sReturn char(17);
  set sReturn=lpad(hex(sBit),12,'0');
  set sReturn=concat_ws(':', substr(sReturn,1,2), substr(sReturn,3,2), substr(sReturn,5,2), substr(sReturn,7,2), substr(sReturn,9,2), substr(sReturn,11,2));
  return sReturn;
end//

delimiter ;

/*
mysql> create table ether_table (b bit(48), a char(17), index(b), index(a));
Query OK, 0 rows affected (0.67 sec)

mysql> insert into ether_table values (rw_ether_atob('00:CD:EF:00:CD:EF'),'00:CD:EF:00:CD:EF'); 
Query OK, 1 row affected (0.01 sec)

mysql> select rw_ether_btoa(b), a from ether_table
    -> where b=rw_ether_atob('00:CD:EF:00:CD:EF');
+-------------------+-------------------+
| rw_ether_btoa(b)  | a                 |
+-------------------+-------------------+
| 00:CD:EF:00:CD:EF | 00:CD:EF:00:CD:EF |
+-------------------+-------------------+
1 rows in set (0.01 sec)

mysql> explain select rw_ether_btoa(b), a from ether_table
    -> where b=rw_ether_atob('00:CD:EF:00:CD:EF');
+----+-------------+-------------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table       | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | ether_table | ALL  | NULL          | NULL | NULL    | NULL | 1002 | Using where |
+----+-------------+-------------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

*/
Wish this helps. regards, Stanley Huang

Comments

Post a Comment

Popular posts from this blog

[Level 1] Rar tool for Solaris.

Sometimes we will got a rar compressed file from our friends, but there is no default tool in OpenSolaris to decompress the rar filess. We can got a Solaris Intel version from rarlab . The steps as the following: 1. down load the rar tools from rarlab. http://www.rarlab.com/rar_add.htm 2. uncompressed the source. # gunzip ./unrar-sol-intel.gz 3. move it to the location you used to. # mv /unrar-sol-intel.gz /usr/bin/unrar 4. try to decomprss # unrar x ./test.rar Wish this helps. regards, Stanley Huang
Read more

[Level 2] iif in Python

If you want to implement iif in Python, please refer to the following sample code: #!/bin/env python def iif(b): return 'It\'s True' if b else 'It\'s False' print iif(True) print iif(False) And the following sample, guess what will you get! #!/bin/env python b = True print 'b: %s' % 'Ture' if b else 'False' b = False print 'b: %s' % 'Ture' if b else 'False' Result: $ ./iif.py b: Ture False The second result is "False", not "b: False". Why??? Because the prededence of string concatenation is higher than if. Therefore you need to use () to make it correct. #!/bin/env python b = True print 'b: %s' % 'Ture' if b else 'False' b = False print 'b: %s' % ('Ture' if b else 'False') Wish this helps. regards, Stanley Huang
Read more