Mysql – is it possible to do a reverse pattern search in a database

full-text-searchMySQL

I have 2 database tables with rows containing strings. If I combine two strings, one from each table, as input I need to be able to find where the parts of the strings are located in each table.

For example:

table 1 has "AB" at row 3
table 2 has "ED" at row 4

If I give the input as "ABED" I need the two rows to be returned.

Can this be done using an SQL command?

Edit: We do not know the sub-strings. We only know the string ABED.So we don't know if the first table has "ABE" in it or "AB" in it and which part of the string is in the second table.

Best Answer

This is what I can come up with (untested).

You have to be able to tell the server that you're searching for "AB" and "ED" separately - otherwise you will get a very large number of possible combinations very quickly.

SELECT t1.*, t2.* 
FROM table1 t1, table2 t2
WHERE t1.t1_field LIKE '%AB%' 
OR t1.t2_field LIKE '%ED%'.

If you require different substring sets per substring, then you will have to use your programming environment (PHP, Java, &c.) to split the main input search string ("ADED") into its components.

[EDIT - in reponse to the OP]

Are these random values or do you have a (limited) specific list? If you have more than ~ 25, then I suggest you go with your programming environment, otherwise the SQL will become very messy.

Just a thought, if you have a limited number of these substrings (at a specific part of the field - i.e. 3 characters in? Or similar), you could create a joining table with the substring and the PRIMARY KEY of the main table to get your lookups done faster.

Are these codes you are decomposing? We really need to see the entire scope of your data to get an idea of what would be the best solution.

Related Question