Well yes I agree the stored format isn't ideal, but unfortunately I have no control over that. These are hard drive serial numbers and that is the format that Microsoft WMI spits them out in. It's possible that the Spiceworks application could be modified to decode those hex strings it gets back from WMI before it saves it in the database, but again, that isn't in my control either. :(
It's disappointing there isn't a simple "unhex()" function to do the reverse of the already existing function. That would be a very elegant solution to this issue, as I am so close. I will investigate the dynamic extension thing tomorrow. I don't have any experience with that, so I'm not sure if it would work but it's worth looking into. In the worst case scenario I could add PHP into the mix and open the SQLite databases directly to do a query + host processing. I just hate to break out of the confines of the Spiceworks application and it's built in reporting capabilities because I was hoping to share my finished report back with their community as it would be very useful to a lot more folks. Thanks, Jim On Wed, Apr 18, 2012 at 5:47 PM, Roger Binns <rog...@rogerbinns.com> wrote: > -----BEGIN PGP SIGNED MESSAGE----- > Hash: SHA1 > > On 18/04/12 14:39, Jim Sanders wrote: > > I found that, but I need essentially the reverse of that. The data > > stored in SQL is 3061626364 and I need to convert it back to 0abcd > > (from your example) > > The correct fix is to get them to stop storing strings that way :-) > Strings can and should be stored as strings. > > You would need to register a user defined function with SQLite that takes > the hex encoding and returns a string. There is no other solution I can > think of as you ruled out a 'host' language. > > It is possible to make dynamically loaded extensions that can provide the > function although it will also require SQLite to have been called to > enable them which is unlikely. > > If your final output can be html then you could output the field as is, > and have some Javascript that finds them and converts to text. > > Roger > -----BEGIN PGP SIGNATURE----- > Version: GnuPG v1.4.11 (GNU/Linux) > > iEYEARECAAYFAk+PNowACgkQmOOfHg372QSCwgCgp9jYnBJ09zhrNNg6DYy9pBbI > kuEAoKl0VFB5eghuYe6+14Xx6dO0cigE > =4WcZ > -----END PGP SIGNATURE----- > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
