Find two matches in a field

Question

Find two matches in a field

Navigation

#1 by (1 votes)

0

I have a table with two fields and I want to draw the matches in another field from another table. One field is the beginning and the other the end of the same field.

Table1

CampoA can end or not with _
CampoB always starts with _

For example:

CampoA     CampoB    CampoC
-------------------------------
HOLA_      _ADIOS     1
HOLA       _BYE       2
HOLA       _ADIOS     3

Table2

CampoAB  
---------------------------
HOLA_1S_ADIOS
HOLA_ADIOS
HOLA_BYE

The result I need would be:

CampoAB        CampoC
-------------------------------
HOLA_ADIOS       1 
HOLA_1S_ADIOS    2
HOLA_BYE         3

The simplest thing, looking for likes does not work, because when it's HELLO, it's also HELLO _

I tried to create the string to search by like "like concat (field1," \ ", field2). With regexp I have searched for the hello with and without" "to search in two phases .

I'm trying different ways, in the end the more functions I use the slower the search becomes. If someone could help me, or tell me which is the best way.

Thanks

mysql query

asked by tasiaylalo 18.06.2018 в 15:26

source

1 answer

Retrieve select input value Problem Sendkeys.Send ({"ENTER"})

score 1 · Accepted Answer

One way to get the data out, if I have correctly understood what you want, can be with a JOIN that use the like for both fields as a condition:

SELECT T2.CampoAB, T1.CampoC
FROM Tabla2 T2 
JOIN Tabla1 T1 ON T2.CampoAB LIKE CONCAT(T1.CampoA, '%') AND T2.CampoAB LIKE CONCAT('%' + T1.CampoB)

In this case if the same CampoAB meets the conditions of for example two cases of the Tabla1 , you will get two results with their corresponding values of CampoC , which is fine if you are interested in all the possible results . To further restrict this result it would be necessary to know what criteria you would apply.
I hope it serves you.

Editing: excluding matches, if the special character '_' is included in the final string, it is not in the initial string, that is, it can not be part of both fields. In this case we look first with the condition that they meet the final field and then ask if what is left of this field meets the criteria of the initial field.

SELECT T2.CampoAB, T1.CampoC
FROM Tabla2 T2 
JOIN Tabla1 T1 ON T2.CampoAB LIKE CONCAT('%' + T1.CampoB)
AND LEFT(T2.CampoAB, (INSTR(T2.CampoAB,T1.CampoB)-1)) LIKE CONCAT(T1.CampoA, '%')

Which one of the fields commands the other, that is, which is the first condition is something that will depend on the design.