Search code examples
mysqlregexreplaceinner-join

REPLACE function not working on SELECT using an INNER JOIN

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"}]

Solution

  • 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.