Geek asks and answers

MySQL Search w / Ordered Wildcards and Retrieving Their Values

Is it possible to do a pattern search and extract wildcard values ​​and put them in the MySQL result? Preferably, I would also like to outline wildcards. Keep reading to understand 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 do not store multiple values ​​in a single database field. See Here: Does a delimited list in a database column really keep that bad?

Instead, I want to know if it is possible to have wildcards in the database, and then compare this to user input. In other words, I would like to enter user input, for example 2.bla.^7or similar, and then return the corresponding line -%.%.%. Then I would like to take these wildcards and return them as part of the MySQL result. Below is an example PHP mysql_fetch_assoc()based on what I'm looking for. Obviously, the result does not need to be laid out that way, but this is the result that I am 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
    )
)

, , , , . , ! , , , ! , , .

!

+1
2

. :

1) 3 like_pattern, regexp_pattern placeholders. content PHP, , / .

  • like_pattern VARCHAR(N) - LIKE
  • regexp_pattern VARCHAR(N) - Regexp PHP
  • placeholders VARCHAR(N) - ,

:

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

2) like_pattern:

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

, , , , , , , .

3) "Hey Nathanael". regexp_pattern PHP preg_match() ereg(). .

4) placeholders.

.

+1

:

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

sqlfiddle.

+2

More articles:


All Articles