Question 21060 in Mysql Question posted in
The official MySQL documentation can be found here.

REPLACE function not working on SELECT using an INNER JOIN – Mysql

I can’t get the REPLACE function to work on my SQL command shown here. I want it to take my search term (55512) and search for this in the field using the modified field from the regexp to remove all non-alphanumeric fields.

SELECT 
  `Customers`.`id` AS `Customers__id`,
  `Contact`.`id` AS `Contact__id`, 
  `Contact`.`customer_id` AS `Contact__customer_id`, 
  `Contact`.`full_name` AS `Contact__full_name`, 
  `Contact`.`phones` AS `Contact__phones`
FROM 
  `customers` `Customers` 
  INNER JOIN `contacts` `Contact` ON `Contact`.`id` = (`Customers`.`contact_id`) 
WHERE 
  REPLACE(
    `Contact`.`phones`, "[^a-zA-Z0-9]", 
    ""
  ) LIKE '%55512%' 
LIMIT 
  20 OFFSET 0

So what I want to do is be able to search "55512" and have it match if the Contact.phones field contains "999-555-1212" or "(999) 555-1212". The Contact.phones field is stored as a JSON array in case this is relevant, but I would expect the above SQL command to remove all brackets, curly braces, etc and just search for the string.

When I do the above search in MySQL, it returns zero results, but there is a result that contains: [{"value":"999-555-1212","label":"Primary"}]

Tags:

2

Answers


  1. 0

    The problem is that the REPLACE function does not work with regex, but attempts to match strings.

    You can solve this problem in two ways:

    WHERE 
  REGEXP_REPLACE(
    `Contact`.`phones`, "[^a-zA-Z0-9]", 
    ""
  ) LIKE '%55512%'
    • keeping the REPLACE function, but replacing dashes only with the empty string.
    WHERE 
  REPLACE(
    `Contact`.`phones`, "-", 
    ""
  ) LIKE '%55512%'

    Check the demo here.

    Login or Signup to reply.
  2. 0 
    WHERE phones RLIKE '555-?12'

    Would also work since the only extraneous characters would be a dash in one spot.

    Both this and the answer from lemon will require a full table scan. That is, either will be slow if the table is big.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search