1

Is it possible to do a wildcard search, and extract the values of the wildcards and put them into a MySQL result? Preferably, I'd also like to label the wildcards. Continue reading to see what I mean.

EXAMPLE: Consider a database table with the following structure.

+----+-------------------+
| id | content           |
+========================+
| 1  |  %1.%2.%3         |
+----+-------------------+
| 2  |  Hey %name.       |
+----+-------------------+

First, note that this is just an example. I am not storing multiple values in a single database field. See here: Is storing a delimited list in a database column really that bad?

Instead, I want to know if it is possible to have wildcards in a database, and then compare that with user input. In other words, I would like to take user input, such as 2.bla.^7 or similar, and then return that respective row--%.%.%. I would then like to take those wildcards, and return them as part of the MySQL result. Below is an example PHP mysql_fetch_assoc() result based on what I'm looking for. The result obviously doesn't need to be laid out exactly like this, but this is the sort of result that I'm looking for.

EXAMPLE:

/************************
 * Input:    2.bla.^7   *
 ************************/
Array
(
    [0] => Array
    (
        [id] => 1,
        [content] => %1.%2.%3
    ),

    [1] => Array
    (
        [1] => 2,
        [2] => bla,
        [3] => ^7
    )
)

/************************
 * Input:   Hey Matt.   *
 ************************/
Array
(
    [0] => Array
    (
        [id] => 2,
        [content] => Hey %1.
    ),

    [1] => Array
    (
        [name] => Matt
    )
)

I understand that what I'm looking for is likely fairly complicated, or specialized for my unique case. If you can point me in the right direction, I would hugely appreciate it! I don't even know if this is possible--if it's not, an alternative solution would be greatly appreciated! However, I want the database structure to stay as close to the same as possible.

Thank you so much!

Community
  • 1
  • 1
Nathanael
  • 6,893
  • 5
  • 33
  • 54
  • Actually I think what your talking about is very similar to the basis behind a lot of content translation code. You store a string such as "Welcome back, %USERNAME%. Your carriage awaits you.". This is stored in the database and translated versions of the same string are stored for each language. Each translated string contains that variable placeholder. So that placeholder can be swapped out whenever you load the string with the variable value. – Patrick May 21 '12 at 23:02
  • That said, what you stated above sounds like the reverse of this. Can you tell me why you would want to do this? What is the point? It seems like you are expecting to input the result string and get back the placeholder structure of that string with the value that the placeholder has been replaced to. Seems very backwards to me. – Patrick May 21 '12 at 23:02
  • It's for an elaborate, rapid-development artificial intelligence simulator I have been developing. When certain patterns are matched based on user input, I want to ultimately store the wildcard values for later use. – Nathanael May 21 '12 at 23:21
  • If you don't want to do the pattern-matching in code and you're willing to change horses, **Postgres** will match regular expressions (with capturing groups) for you in the database. See the doc about Posix regular expressions and the `regexp_matches` function. http://www.postgresql.org/docs/8.3/static/functions-matching.html#FUNCTIONS-POSIX-REGEXP – Peter Dixon-Moses May 26 '12 at 16:44

2 Answers2

2

Don't you just want to do: 

SELECT * FROM patterns WHERE 'user input' LIKE content;

See it on sqlfiddle.

eggyal
  • 122,705
  • 18
  • 212
  • 237
  • No. :P That doesn't return the wildcard values, nor does it allow for named wildcards. – Nathanael May 21 '12 at 23:16
  • Ah, I understand what you're asking now. You need to user regular expression pattern matching within PHP to extract the matching terms. Off the top of my head I'm not sure of the best way to generalise named subpatterns... if something comes to me I'll post back. – eggyal May 21 '12 at 23:21
1

Indeed there is no ready-to-go solution. Here is what I would do:

1) Add 3 special columns like_pattern, regexp_pattern and placeholders. Their values are calculated from the column content either in PHP before loading data into the table, or in BEFORE INSERT/UPDATE TRIGGERs.

  • like_pattern VARCHAR(N) - LIKE pattern for reverse search
  • regexp_pattern VARCHAR(N) - Regexp pattern for extracting parts later in PHP
  • placeholders VARCHAR(N) - Comma-separated list of placeholder names

Here is the example: 

+----+-------------+--------------+--------------------+--------------+
| id | content     | like_pattern | regexp_pattern     | placeholders |
+====+=============+==============+====================+==============+
| 1  |  %1.%2.%3   | %.%.%        | ^(.*)\.(.*)\.(.*)$ | 1,2,3        |
+----+-------------+--------------+-+------------------+--------------+
| 2  |  Hey %name. | Hey %.       | ^Hey (.*)\.$       | name         |
+----+-------------+--------------+--------------------+--------------+

2) For a given string search record using like_pattern:

SELECT * FROM patterns WHERE 'Hey Nathanael.' LIKE like_pattern;

Be aware that simple index can not speed up this query because we perform a reverse search, so depending on how big your table is you probably will need a different solution.

3) Match the string 'Hey Nathanael.' against returned regexp_pattern using PHP's preg_match() or ereg(). You will get placeholder values.

4) Combine them with placeholder names from the column placeholders.

That's it.

spatar
  • 550
  • 1
  • 5
  • 15
  • This is an interesting solution, but I think it will actually work! Thanks a lot for the pointer--very clever! I appreciate it! – Nathanael May 27 '12 at 21:58