mysql – query to extract report from book register

If the barcode column is VARCHAR and begins with "HYD", CAST AS UNSIGNED will cause a value of HYD123 to result in 0.

The non-numeric characters of the string would need to be removed prior to casting the value as an integer.

This can be achieved by trimming the leading text "HYD" from the barcode.

CAST(TRIM(LEADING 'HYD' FROM barcode) AS UNSIGNED)

Otherwise, if the prefix is always 3 characters, the substring position of barcode can be used.

CAST(SUBSTR(barcode, 4) AS UNSIGNED)

If any other non-numeric characters are contained within the string, such as HYD-123-456-789, HYD123-456-789PT, HYD123-456.789, etc, they will also needed to be removed, as the type conversion will treat them in unexpected ways.
In addition, any leading 0’s of the resulting numeric string value will be truncated from the resulting integer, causing 0123 to become 123.
For more details on how CAST functions see: 12.3 Type Conversion in Expression Evaluation

Examples db<>fiddle

CREATE TABLE tester (
    barcode varchar(255)
);

INSERT INTO tester(barcode) 
VALUES ('HYD123'), ('HYD0123'), ('HYD4231');

Results

SELECT cast(barcode AS UNSIGNED)
FROM tester;

SELECT CAST(TRIM(LEADING 'HYD' FROM barcode) AS UNSIGNED)
FROM tester;

SELECT barcode
FROM tester
WHERE CAST(TRIM(LEADING 'HYD' FROM barcode) AS UNSIGNED) BETWEEN 120 AND 4232;

SELECT CAST(SUBSTR(barcode, 4) AS UNSIGNED)
FROM tester;

SELECT barcode
FROM tester
WHERE CAST(SUBSTR(barcode, 4) AS UNSIGNED) BETWEEN 120 AND 4232;

JOIN optimization

To obtain the expected results, you most likely want an INNER JOIN of the items table with an ON criteria matching the desired barcode range condition. Since INNER JOIN is the equivalent of using WHERE oo.barcode IS NOT NULL, as is the case with your current criteria – NULL matches within the items table are already being excluded.

INNER JOIN items AS oo 
ON oo.biblioitemnumber = o.biblioitemnumber
AND CAST(SUBSTR(oo.barcode, 4) AS UNSIGNED) BETWEEN ? AND ?

Full-Table Scanning

It is important to understand that transforming the column value to suit a criteria will cause a full-table scan that does not benefit from indexing, which will run very slowly.
Instead it is best to store the integer only version of the value in the database to see the benefits of indexing.
This can be accomplished in many ways, such as generated columns.

GROUP BY without an aggregate

Lastly, you should avoid using GROUP BY without an aggregate function. You most likely are expecting DISTINCT or similar form of limiting the record set. Please see MySQL select one column DISTINCT, with corresponding other columns on ways to accomplish this.

To ensure MySQL is not selecting "any value from each group" at random (leading to differing results between query executions), limit the subset data to the distinct biblioitemnumber column values from the available barcode matches. One approach to accomplish the limited subset is as follows.

/* ... */
FROM biblioitems o
INNER JOIN (
    SELECT biblioitemnumber, barcode, booksellerid, enumchron, itemcallnumber 
    FROM items WHERE biblioitemnumber IN(
        SELECT MIN(biblioitemnumber)
        FROM items
        WHERE CAST(SUBSTR(barcode, 4) AS UNSIGNED) BETWEEN ? AND ?
        GROUP BY barcode
    )
) AS oo
ON oo.biblioitemnumber = o.biblioitemnumber
LEFT JOIN biblio ooo ON ooo.biblionumber=o.biblionumber
LEFT JOIN aqorders oooo ON oooo.biblionumber=o.biblionumber
LEFT JOIN currency ooooo ON ooooo.currency=oooo.currency
LEFT JOIN aqinvoices oooooo ON oooooo.booksellerid=oo.booksellerid
LEFT JOIN aqbooksellers ooooooo ON ooooooo.id=oo.booksellerid
ORDER BY oo.barcode ASC