Regex pattern doesn't work with MySQL regexp

Question

I do have the below regex that works fine with Regex tester (thanks to horcrux). However, it returns no matches when I use it with MySQL regexp

select query from search s where s.query regexp '^((&|^)(serviceType=SALE|propertyType=HOUSE|city=1)){1,3}$'

The above should match the below

serviceType=SALE&propertyType=HOUSE&city=1
propertyType=HOUSE&serviceType=SALE&city=1
city=1&propertyType=HOUSE&serviceType=SALE
city=1&serviceType=SALE&propertyType=HOUSE
serviceType=SALE&propertyType=HOUSE
serviceType=SALE

but not these

serviceType=SALE&propertyType=HOUSE&city=2
propertyType=HOUSE&city=2&serviceType=SALE
city=2&propertyType=HOUSE&serviceType=SALE
serviceType=SALE&propertyType=FARM&city=1
serviceType=SALE&propertyType=UNIT
serviceType=RENTAL&propertyType=HOUSE
serviceType=RENTAL

Does `^(serviceType=SALE|propertyType=HOUSE|city=1)(&(serviceType=SALE|propertyType=HOUSE|city=1)){0,2}$` work? What is the error? Is there any? — Wiktor Stribiżew, Apr 16 '19 at 12:30
Works fine for me: https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=1a2d887c9a7e493ade8f6443ba035fe5 — Nick, Apr 16 '19 at 12:32
Thanks for the quick reply. It works in the tester but still no matches in MySQL regexp unfortunately — zoro74, Apr 16 '19 at 12:32
Works with MySQL 5.7 too: https://www.db-fiddle.com/f/8CYKKJ2gs1EUQ57vo1Gmyu/0 — Nick, Apr 16 '19 at 12:33
Thanks @nick it actually works now when I used a query to insert test values to the table. Originally I copy/pasted values directly to the search table which seems to have caused the issue. Thanks again to the quick reply — zoro74, Apr 16 '19 at 12:43
@zoro74 maybe you accidentally got some whitespace in the values when you were testing. Anyway, good to hear it is working. — Nick, Apr 16 '19 at 12:47

score 0 · Answer 1 · answered Apr 16 '19 at 12:34

Looks like you rather want the string to match all of the key value pairs not just any, which is what you current pattern matches.

Try ANDing the REGEXP operations, one for each key value pair.

s.query REGEXP '(&|^)serviceType=SALE(&|$)'
        AND s.query REGEXP '(&|^)propertyType=HOUSE(&|$)'
        AND s.query REGEXP '(&|^)city=1(&|$)'

Raymond Nijland · Answer 2 · 2019-04-16T13:34:09.197

The hard workaround without using regex, this post is just meant to show it's possible.
The trick is to make a MySQL number generator and use nested SUBSTRING_INDEX() functions to cut the strings up into tokens.

Query

 SELECT 
   separated_key_values.query
 , SUBSTRING_INDEX(
       SUBSTRING_INDEX(
           separated_key_values.separated_property
         , '='
         , 1
       )
      ,'='
     , -1
   ) AS property_key
 , SUBSTRING_INDEX(
       SUBSTRING_INDEX(
           separated_key_values.separated_property
         , '='
         , 2
       )
      ,'='
     , -1
   ) AS property_value   
FROM (

SELECT 
  DISTINCT
     search.query 
   , SUBSTRING_INDEX(
       SUBSTRING_INDEX(
           search.query
         , '&'
         , number_generator.row_number
       )
      ,'&'
     , -1
   ) separated_property
FROM (
  SELECT 
   @row := @row + 1 AS row_number
  FROM (
    SELECT 0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9
  ) row1
  CROSS JOIN (
    SELECT 0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9
  ) row2  
  CROSS JOIN (
    SELECT @row := 0 
  ) AS init_user_params
) AS number_generator
CROSS JOIN 
 search 

) AS separated_key_values
ORDER BY 
 separated_key_values.query ASC

Result

| query                                      | property_key | property_value |
| ------------------------------------------ | ------------ | -------------- |
| city=1&propertyType=HOUSE&serviceType=SALE | city         | 1              |
| city=1&propertyType=HOUSE&serviceType=SALE | serviceType  | SALE           |
| city=1&propertyType=HOUSE&serviceType=SALE | propertyType | HOUSE          |
| city=1&serviceType=SALE&propertyType=HOUSE | city         | 1              |
| city=1&serviceType=SALE&propertyType=HOUSE | propertyType | HOUSE          |
| city=1&serviceType=SALE&propertyType=HOUSE | serviceType  | SALE           |
| city=2&propertyType=HOUSE&serviceType=SALE | serviceType  | SALE           |
| city=2&propertyType=HOUSE&serviceType=SALE | city         | 2              |
| city=2&propertyType=HOUSE&serviceType=SALE | propertyType | HOUSE          |
| propertyType=HOUSE&city=2&serviceType=SALE | city         | 2              |
| propertyType=HOUSE&city=2&serviceType=SALE | serviceType  | SALE           |
| propertyType=HOUSE&city=2&serviceType=SALE | propertyType | HOUSE          |
| propertyType=HOUSE&serviceType=SALE&city=1 | city         | 1              |
| propertyType=HOUSE&serviceType=SALE&city=1 | serviceType  | SALE           |
| propertyType=HOUSE&serviceType=SALE&city=1 | propertyType | HOUSE          |
| serviceType=RENTAL                         | serviceType  | RENTAL         |
| serviceType=RENTAL&propertyType=HOUSE      | propertyType | HOUSE          |
| serviceType=RENTAL&propertyType=HOUSE      | serviceType  | RENTAL         |
| serviceType=SALE                           | serviceType  | SALE           |
| serviceType=SALE&propertyType=FARM&city=1  | serviceType  | SALE           |
| serviceType=SALE&propertyType=FARM&city=1  | propertyType | FARM           |
| serviceType=SALE&propertyType=FARM&city=1  | city         | 1              |
| serviceType=SALE&propertyType=HOUSE        | propertyType | HOUSE          |
| serviceType=SALE&propertyType=HOUSE        | serviceType  | SALE           |
| serviceType=SALE&propertyType=HOUSE&city=1 | city         | 1              |
| serviceType=SALE&propertyType=HOUSE&city=1 | propertyType | HOUSE          |
| serviceType=SALE&propertyType=HOUSE&city=1 | serviceType  | SALE           |
| serviceType=SALE&propertyType=HOUSE&city=2 | propertyType | HOUSE          |
| serviceType=SALE&propertyType=HOUSE&city=2 | city         | 2              |
| serviceType=SALE&propertyType=HOUSE&city=2 | serviceType  | SALE           |
| serviceType=SALE&propertyType=UNIT         | propertyType | UNIT           |
| serviceType=SALE&propertyType=UNIT         | serviceType  | SALE           |

see demo

After that it just as simple as adding as conditional aggregation.

Query

SELECT 
  separated_key_values.query

, 

 (
   SUM(separated_key_values.property_key = 'serviceType') > 0
 AND
   SUM(separated_key_values.property_value = 'SALE') > 0

 ) AS has_serviceType_SALE

, 

 (
   SUM(separated_key_values.property_key = 'propertyType') > 0
 AND
   SUM(separated_key_values.property_value = 'HOUSE') > 0

 ) AS has_propertyType_HOUSE

, 

 (
   SUM(separated_key_values.property_key = 'City') > 0
 AND
   SUM(separated_key_values.property_value = '1') > 0

 )  AS has_City_1

, (

 (
   SUM(separated_key_values.property_key = 'serviceType') > 0
 AND
   SUM(separated_key_values.property_value = 'SALE') > 0

 ) 

 + 

 (
   SUM(separated_key_values.property_key = 'propertyType') > 0
 AND
   SUM(separated_key_values.property_value = 'HOUSE') > 0

 ) 

 + 

 (
   SUM(separated_key_values.property_key = 'City') > 0
 AND
   SUM(separated_key_values.property_value = '1') > 0

 )   

  ) AS has_mask 

, COUNT(*)

FROM (
SELECT 
   search_alias.query
 , SUBSTRING_INDEX(
       SUBSTRING_INDEX(
           search_alias.separated_property
         , '='
         , 1
       )
      ,'='
     , -1
   ) AS property_key
 , SUBSTRING_INDEX(
       SUBSTRING_INDEX(
           search_alias.separated_property
         , '='
         , 2
       )
      ,'='
     , -1
   ) AS property_value   
FROM (

SELECT 
  DISTINCT
     search.query 
   , SUBSTRING_INDEX(
       SUBSTRING_INDEX(
           search.query
         , '&'
         , number_generator.row_number
       )
      ,'&'
     , -1
   ) separated_property
FROM (
  SELECT 
   @row := @row + 1 AS row_number
  FROM (
    SELECT 0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9
  ) row1
  CROSS JOIN (
    SELECT 0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9
  ) row2  
  CROSS JOIN (
    SELECT @row := 0 
  ) AS init_user_params
) AS number_generator
CROSS JOIN 
 search 

) AS search_alias
) AS separated_key_values
GROUP BY 
 separated_key_values.query
HAVING 
 has_mask = COUNT(*)

Result

| query                                      | has_serviceType_SALE | has_propertyType_HOUSE | has_City_1 | has_mask | COUNT(*) |
| ------------------------------------------ | -------------------- | ---------------------- | ---------- | -------- | -------- |
| city=1&propertyType=HOUSE&serviceType=SALE | 1                    | 1                      | 1          | 3        | 3        |
| city=1&serviceType=SALE&propertyType=HOUSE | 1                    | 1                      | 1          | 3        | 3        |
| propertyType=HOUSE&serviceType=SALE&city=1 | 1                    | 1                      | 1          | 3        | 3        |
| serviceType=SALE                           | 1                    | 0                      | 0          | 1        | 1        |
| serviceType=SALE&propertyType=HOUSE        | 1                    | 1                      | 0          | 2        | 2        |
| serviceType=SALE&propertyType=HOUSE&city=1 | 1                    | 1                      | 1          | 3        | 3        |

see demo

You could also place the column output into the HAVING clause so you don't output those columns.
See demo

Note
This will not scale on large tables most likely the regex query will also not scale because indexes most likely can't be used.

A workaround method might be to use a temporary table with a correct index and use the first query to prefill and do the conditional aggregation on the temporary table which is indexed.

Regex pattern doesn't work with MySQL regexp

2 Answers2