search columns which contains letter and numbers mysql field-CodePudding

I have 2 fields in my table one is called BarcodeStart AND BarcodeEnd what i want to do is search for a Barcode for example G1001 which appears between the BarcodeStart AND BarcodeEnd field. The table i have created is below

OrderID	BarcodeStart	BarcodeEnd
1	G1000	G1110
2	G2000	G2010

So if i search for G1001 i would like the query to return back Order ID 1 as G1001 is between the BarcodeStart AND BarcodeEnd field. I have tried the query below

SELECT OrderID  FROM table WHERE 
    (
        (LOWER(BarcodeEnd) = 'G1001' )
    or 
        (LOWER(BarcodeStart) = 'G1001' )
    or
        (
            LOWER(BarcodeStart) > 'G1001' and 
            LOWER(BarcodeEnd) < 'G1001'
        )
    )

by no luck the BarcodeStart and BarcodeEnd are varchar(100)

CodePudding user response：

Your comparisons are backwards. You want 'G1001' > BarcodeStart. But you can simplify all of this by using the BETWEEN operator.

SELECT OrderID  
FROM mytable 
WHERE 'G1001' BETWEEN BarcodeStart AND BarcodeEnd

DEMO

CodePudding user response：

You have to use SUBSTRING_INDEX() function (https://www.w3schools.com/sql/func_mysql_substring_index.asp).

SELECT *
FROM (SELECT `OrderId`, 
       SUBSTRING_INDEX(`BarcodeStart`, 'G', -1) AS `Start`,
       SUBSTRING_INDEX(`BarcodeEnd`, 'G', -1) AS `End`
       FROM `Table1`) as `Table2`
WHERE `Start`<=SUBSTRING_INDEX('G1001', 'G', -1)
  AND `End`>=SUBSTRING_INDEX('G1001', 'G', -1);

G10001 is the input value.

I hope it helps