Hi I'm trying to create a simple query to find the products with more than 5000mAh since this a type VARCHAR2 I'm unable to set the condition with just numbers. I have tried using TO_NUMBER to convert string to number but I get.

ORA-01722: invalid number

Here is the query

SELECT Product.product_name, Product.battery FROM Product WHERE battery >= TO_NUMBER('5000');

INSERT INTO Product (product_id,product_categ_type_id,product_name,price,stock_qty,screen_size,cpu,ram, battery,colour)

VALUES((110,2,'Samsung 9 Plus',267,191,'10.1','Octa 1.3GHz','6GB', '3500mAh', 'Blue')

INSERT INTO Product (product_id,product_categ_type_id,product_name,price,stock_qty,screen_size,cpu,ram, battery,colour)

VALUES((116,2,'Huawei P50Pro',393,138,'6.0','Octa 2.5GHz','16GB', '6400mAh', 'Black')

INSERT INTO Product (product_id,product_categ_type_id,product_name,price,stock_qty,screen_size,cpu,ram, battery,colour)

VALUES((194,3,'Samsung Galaxy Tab',398,138,'10.10','Octa 1.6GHz','2GB', '7300mAh', 'Black')

CodePudding user response：

You need a REGEXP_SUBSTR for this instead of TO_NUMBER only -

SELECT Product.product_name, Product.battery
  FROM Product
 WHERE REGEXP_SUBSTR(battery, '\d ') >= 5000;

Demo.

CodePudding user response：

When you do:

SELECT Product.product_name, Product.battery FROM Product WHERE battery >= TO_NUMBER('5000');

... you are converting the wrong side of the comparison, and forcing an implicit conversion of your column value to a number; it should be:

SELECT Product.product_name, Product.battery FROM Product WHERE TO_NUMBER(battery) >=5000;

... but that will still get the same error "ORA-01722: invalid number" because you have unrecognised non-digit characters.

Rather than using a (possibly expensive and slow) regular expression to extract the digits, if the battery is always quoted in 'mAh' exactly as you've shown then you could treat that as if was was a currency:

SELECT Product.product_name, Product.battery
FROM Product
WHERE TO_NUMBER(battery, '999999L', 'nls_currency=mAh') >=5000;

fiddle