Detect numeric values from VARCHAR2 oracle

Question

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')

Ankit Bajpai · Accepted Answer · 2022-12-06 14:41:30Z

1

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.

answered Dec 6, 2022 at 14:41

Ankit Bajpai

13.6k4 gold badges27 silver badges43 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Alex Poole · Accepted Answer · 2022-12-06 15:23:09Z

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;

PRODUCT_NAME	BATTERY
Huawei P50Pro	6400mAh
Samsung Galaxy Tab	7300mAh

fiddle

Collectives™ on Stack Overflow

Detect numeric values from VARCHAR2 oracle

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related