PHP Versions Question posted in
You can find a list of all versions here.

Php versions – MySQL select rows where cell value equals number where non-digit characters are dropped

Problem: some product sku look like this “67062-4D” or “MOD602-04-N”. So task is to find sku by its digits when user uses search, e.g. “67062-4D” by “670624” and “MOD123-45-N” by “12345”.
Solution: I managed to solve this by repeting [^0-9]*1 in php to create a regexp (php and mysql is in use), but realy want to know if where is a universal or more natural solution. Otherwise just posting this solution.
This example finds “MOD123-45-N”

SELECT product_id WHERE sku REGEXP '^[^0-9]*1[^0-9]*2[^0-9]*3[^0-9]*4[^0-9]*5[^0-9]*$'

update:
version 10.1.40-MariaDB

Tags:

2

Answers


  1. 0

    You could use REGEXP_REPLACE to remove all non-digit characters from the string and then compare it to the input number e.g.

    SELECT product_id WHERE REGEXP_REPLACE(sku, '[^0-9]', '') = '12345'

    Demo on dbfiddle

    Login or Signup to reply.
  2. 0

    With regexp_replace():

    select product_id 
from tablename
where regexp_replace(sku, '\D', '') LIKE concat('%', search, '%')

    or to find exact match:

    select product_id 
from tablename
where regexp_replace(sku, '\D', '') = search
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search