Joanne Pham wrote:
>
> Can we convert these sql statement to function/store procedure so we can pass
> in the number and the return value back the character format.
> For example : ConvertMAC(29672054730752 ) and the return value back :
> 00:30:48:90:FC:1A
No, you can't create user defined functions in SQL, and SQLite does not
support stored procedures.
You could create a view that returns the same columns as the base table
with the mac address column converted to a string using this SQL
expression.
Given
create table t (id, mac integer, data text);
You could create a view like this
create view tv as
select id,
substr('0123456789ABCDEF', ((mac >> 4) & 15) + 1, 1) ||
substr('0123456789ABCDEF', ((mac >> 0) & 15) + 1, 1) ||
':' ||
substr('0123456789ABCDEF', ((mac >> 12) & 15) + 1, 1) ||
substr('0123456789ABCDEF', ((mac >> 8) & 15) + 1, 1) ||
':' ||
substr('0123456789ABCDEF', ((mac >> 20) & 15) + 1, 1) ||
substr('0123456789ABCDEF', ((mac >> 16) & 15) + 1, 1) ||
':' ||
substr('0123456789ABCDEF', ((mac >> 28) & 15) + 1, 1) ||
substr('0123456789ABCDEF', ((mac >> 24) & 15) + 1, 1) ||
':' ||
substr('0123456789ABCDEF', ((mac >> 36) & 15) + 1, 1) ||
substr('0123456789ABCDEF', ((mac >> 32) & 15) + 1, 1) ||
':' ||
substr('0123456789ABCDEF', ((mac >> 44) & 15) + 1, 1) ||
substr('0123456789ABCDEF', ((mac >> 40) & 15) + 1, 1)
as mac_addr,
data
from t;
Now you can use the view in all your queries and get the string form of
the mac address when ever you need it.
You could also do a join to the view using the id column whenever you
want do get the mac address string in a query that still needs to use
the original mac address as an integer.
select data, mac_addr
from t
join tv on tv.id = t.id
where t.mac in (select ...)
HTH
Dennis Cote
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
