sql query discrepancy-CodePudding

while fetching the order from database, the orderNumber is 8 digit code for example: 20178908. But I found some records having alphabets in them: Example F2830P921 and I need to delete those orders. I used the query:

"SELECT {pk} FROM {order} where {ordernumber}=" orderModel.getCode();

Can anyone please help me to modify the query to take order number which has alphabets?

CodePudding user response：

Assuming you actually want to delete these records, then you may use a delete query with the help of REGEXP:

DELETE
FROM orderTable
WHERE orderNumber REGEXP '[A-Z]';

If, on the other hand, you just want to view your order data sans these records with alpha order numbers, then use:

SELECT *
FROM yourTable
WHERE orderNumber REGEXP '^[0-9] $';