How to check whether integer contains 0 in MySQL?

Question

Im trying to pull bunch of records from MySQL with condition whether the column value contains 0. If I convert to string and check with contains (%0%), it takes more time to execute.. Is there any short way to check on Integer column? Thank you..

Why do you need such check? Performing a string operation on a integer column is always slower than straight artithmetics. Please explain the problem you are trying to solve. — GMB
– GMB, Commented Oct 24, 2020 at 17:08
We are writing a query to display the customer_id,customer full name ,city,pincode,and -- order details (order id,order date, product class desc, product desc, subtotal(product_quantity * product_price)) -- for orders shipped to cities whose pin codes do not have any 0s in them. And sort the output on customer name, order date and subtotal. — Srini K
– Srini K, Commented Oct 24, 2020 at 17:11
Simply add according CHECK constraint over this column into the table structure - and the presence of the value with zero digit in it will be impossible. Recommended check is REGEXP '^[1-9]{6}$'. — Akina
– Akina, Commented Oct 24, 2020 at 19:26
In described situation you must take into account that pin must be > 111110. Because, for example, entered 012345 which will be stored as 12354 will match NOT LIKE '%0%' check. — Akina
– Akina, Commented Oct 24, 2020 at 19:28

vickythegme · Accepted Answer · 2020-10-24 17:47:11Z

0

You can probably run a regex on the integer column. Something like below.

SELECT fieldname FROM tablename WHERE fieldname REGEX '[0]';

About the performance, I believe that LIKE is faster than REGEX. But since your LIKE involves string conversion, this would have some difference in execution time

answered Oct 24, 2020 at 17:47

vickythegme

3672 silver badges14 bronze badges

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

2 Comments

Akina Over a year ago

REGEXP converts the argument to string too.

Svetoslav Marinov Over a year ago

It's supposed to be REGEXP and not REGEX. Also it should be REGEXP '^[0]$' if you want the value to be 0 and not just to contain 0

Gharbad The Weak · Accepted Answer · 2020-10-25 01:53:04Z

@SriniK You can use either POSITION() or LOCATE() in conjunction with CONVERT() to convert the column value into a string and then find if the string "0" exists within the string. Both of these functions return a positive number greater than 0 indicating the index of the substring within your string and they return a 0 if the string isn't found.

Here's how to create a simple test table to illustrate:

CREATE TABLE `testTable` (`id` INTEGER NOT NULL AUTO_INCREMENT, `someNum` INTEGER, PRIMARY KEY(`id`));
                          
INSERT INTO `testTable` (`someNum`)
VALUES
(1),
(10),
(11),
(100),
(111),
(222),
(210);

And here are two queries illustrating both POSITION() and LOCATE():

SELECT `id`,
    `someNum`,
    POSITION('0' IN CONVERT(`someNum`, CHAR))
FROM `testTable`
WHERE POSITION('0' IN CONVERT(`someNum`, CHAR)) > 0
;
                              
SELECT `id`,
    `someNum`,
    LOCATE('0', CONVERT(`someNum`, CHAR))
FROM `testTable`
WHERE LOCATE('0', CONVERT(`someNum`, CHAR)) > 0
;

I have mocked this up on dbfiddle here so you can get a better look at it.

If this doesn't get you what you need or if you don't understand something please leave a comment with details and I'll do what I can to help further. Good luck.

Collectives™ on Stack Overflow

How to check whether integer contains 0 in MySQL?

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related