MySQL - search and natural sorting

Question

I have a stock of items. For example, item number (and value in the data base) is C10364A. The item has a barcode on it. When I scan it with a barcode scanner, the code value is PC10364A.

I would like to make a select input with search. When I input by barcode scanner, how to search and select this exact item?

The second question is about natural sorting.

Presented with values:

{ J1, J3, J18, J41, J12, L1, L13, L8 }

How to sort them naturally? I've tried many queries, but no luck.

Answer 1

[Q1] Searching for Items by Barcode:

To search for items by barcode, you can use the SQL LIKE operator to find items that match the scanned barcode. In your case, if the barcode scanner provides PC10364A, you can use a query similar to this:

SELECT * FROM your_stock_of_items WHERE barcode LIKE 'PC10364A';

This query will return all items that have a barcode matching PC10364A

[Q2] Natural Sorting

Unfortunately, MySQL does not have a built-in natural sort function, so to achieve natural sorting for values like "J1," "J3," "J18," etc., you can split the text and numeric parts and then order by them.

SELECT value FROM your_table -- or your_stock_of_items
ORDER BY 
   CAST(SUBSTRING_INDEX(value, '', -1) AS SIGNED), 
   SUBSTRING_INDEX(value, '', 1);

Here, SUBSTRING_INDEX is used to split the values into the text and numeric parts. Then, we can cast the numeric part to SIGNED to ensure numeric sorting. This will give you the desired natural sorting order for values like "J1," "J3," "J18," etc.