Arithmetic operators on varchar column?
Barcodes are stored in items.barcode as varchar type. Since our standard barcodes are numeric, is there a way to use arithmetic operators (<, >) on this column? items.barcode >= 34258000220000 in a SQL report does not work, though items.barcode LIKE '3425800022%' does. Note that we may have the odd barcode which is not numeric. Cheers, Cab Vinton Sanbornton Public Library
Hi, On Wed, Apr 17, 2013 at 8:14 AM, Cab Vinton <bibliwho@gmail.com> wrote:
Barcodes are stored in items.barcode as varchar type. Since our standard barcodes are numeric, is there a way to use arithmetic operators (<, >) on this column?
items.barcode >= 34258000220000 in a SQL report does not work, though items.barcode LIKE '3425800022%' does.
Note that we may have the odd barcode which is not numeric.
One thing you can do is to explicitly cast the barcode as an integer, e.g. CAST(items.barcode AS UNSIGNED INTEGER) >= 34258000220000 Any barcode values that aren't strictly numeric will be treated as having value 0. Regards, Galen -- Galen Charlton Manager of Implementation Equinox Software, Inc. / The Open Source Experts email: gmc@esilibrary.com direct: +1 770-709-5581 cell: +1 404-984-4366 skype: gmcharlt web: http://www.esilibrary.com/ Supporting Koha and Evergreen: http://koha-community.org & http://evergreen-ils.org
Does items.barcode >= "34258000220000" answers your need ? Sophie -- Responsable support BibLibre + 33 (0)4 91 81 35 08 http://www.biblibre.com Le 17/04/2013 17:14, Cab Vinton a écrit :
Barcodes are stored in items.barcode as varchar type. Since our standard barcodes are numeric, is there a way to use arithmetic operators (<, >) on this column?
items.barcode >= 34258000220000 in a SQL report does not work, though items.barcode LIKE '3425800022%' does.
Note that we may have the odd barcode which is not numeric.
Cheers,
Cab Vinton Sanbornton Public Library
_______________________________________________ Koha-devel mailing list Koha-devel@lists.koha-community.org http://lists.koha-community.org/cgi-bin/mailman/listinfo/koha-devel website : http://www.koha-community.org/ git : http://git.koha-community.org/ bugs : http://bugs.koha-community.org/
Just realized I hadn't responded ... Thank you, Sophie. That does indeed work. Galen's solution (explicitly casting the barcode as an integer) produces the same results. Thanks to both! Cab Vinton Sanbornton PL On Wed, Apr 17, 2013 at 11:36 AM, Sophie Meynieux < sophie.meynieux@biblibre.com> wrote:
Does items.barcode >= "34258000220000" answers your need ?
Sophie
-- Responsable support BibLibre+ 33 (0)4 91 81 35 08http://www.biblibre.com
Le 17/04/2013 17:14, Cab Vinton a écrit :
Barcodes are stored in items.barcode as varchar type. Since our standard barcodes are numeric, is there a way to use arithmetic operators (<, >) on this column?
items.barcode >= 34258000220000 in a SQL report does not work, though items.barcode LIKE '3425800022%' does.
Note that we may have the odd barcode which is not numeric.
Cheers,
Cab Vinton Sanbornton Public Library
_______________________________________________ Koha-devel mailing listKoha-devel@lists.koha-community.orghttp://lists.koha-community.org/cgi-bin/mailman/listinfo/koha-devel website : http://www.koha-community.org/ git : http://git.koha-community.org/ bugs : http://bugs.koha-community.org/
_______________________________________________ Koha-devel mailing list Koha-devel@lists.koha-community.org http://lists.koha-community.org/cgi-bin/mailman/listinfo/koha-devel website : http://www.koha-community.org/ git : http://git.koha-community.org/ bugs : http://bugs.koha-community.org/
participants (3)
-
Cab Vinton -
Galen Charlton -
Sophie Meynieux