CODEWITHSUNDEEP
mysqlsqlmariadb
Lbutlr
Lbutlr

Reputation: 23

How do I search for a string in a cell substring containing a string from another cell in SQL

I am looking to compare the results of 2 cells in the same row. the way the data is structured is essentially this: 

Col_A: table,row,cell   
Col_B: row

What I want to do is compare when Col_A 'row' is the same as Col_B 'row'

SELECT COUNT(*) FROM MyTable WHERE Col_A CONTAINS Col_B;

sample data:

Col_A: a=befd-47a8021a6522,b=7750195008,c=prof    
Col_B: b=7750195008

Col_A: a=bokl-e5ac10085202,b=4478542348,c=pedf
Col_B: b=7750195008

I am looking to return the number of times the comparison between Col_A 'b' and Col_B 'b' is true.

Upvotes: 0

Views: 62

Answers (6)

Antonín Lejsek
Antonín Lejsek

Reputation: 6103

I see You answered Your own question.

SELECT COUNT(*) FROM MyTable WHERE Col_A LIKE CONCAT('%',Col_B,'%');

is good from performance perspective. While normalization is very good idea, it would not improve speed much in this particular case. We must simply scan all strings from table. Question is, if the query is always correct. It accepts for example

Col_A: a=befd-47a8021a6522,ab=7750195008,c=prof    
Col_B: b=7750195008

or

Col_A: a=befd-47a8021a6522,b=775019500877777777,c=prof    
Col_B: b=7750195008

this may be a problem depending on the data format. Solution is quite simple

SELECT COUNT(*) FROM MyTable WHERE CONCAT(',',Col_A,',') LIKE CONCAT('%,',Col_B,',%');

But this is not the end. String in LIKE is interpreted and if You can have things like % in You data You have a problem. This should work on mysql:

SELECT COUNT(*) FROM MyTable WHERE LOCATE(CONCAT(',',Col_B,','), CONCAT(',',Col_A,','))>0;

Upvotes: 2

fabulaspb
fabulaspb

Reputation: 1263

If column Col_a has data with format table,row,cell then search expression will be next: 

SELECT COUNT(*) FROM MyTable AS MT
WHERE SUBSTRING(Col_A, 
                INSTR(Col_A, ',b=') + 3, 
                INSTR(Col_A, ',c=') - INSTR(Col_A, ',b=') + 3) = Col_B

Upvotes: 0

Barmar
Barmar

Reputation: 781592

You can use SUBSTRING_INDEX to extract a delimited field from a column.

SELECT COUNT(*)
FROM MyTable
WHERE Col_B = SUBSTRING_INDEX(SUBSTRING_INDEX(Col_A, ',', 2), ',', -1)

You need to call it twice to get a single field. The inner call gets the first two fields, the outer call gets the last field of that.

Note that this will be very slow if the table is large, because it's not possible to index substrings in MySQL. It would be much better if you normalized your schema so each field is in a separate column.

Upvotes: 0

Lbutlr
Lbutlr

Reputation: 23

This does what I was looking for: 

SELECT COUNT(*) FROM MyTable WHERE Col_A LIKE CONCAT('%',Col_B,'%');

Upvotes: 2

John G
John G

Reputation: 98

The easiest way would be to use the IN operator. 

SELECT COUNT(*) FROM MyTable WHERE Col_A IN (Col_B);

More info on the IN operator: http://www.w3schools.com/sql/sql_in.asp

There's also the SUBSTRING() or MID() (depending on what you're using) function if you know that the substring will be in the same position everytime.

MID()/SUBSTRING() function: http://www.w3schools.com/sql/sql_func_mid.asp

Upvotes: 0

etalon11
etalon11

Reputation: 984

SELECT * FROM MyTable WHERE Col_A = Col_B (AND Col_A = 'cell')

^^ Maybe you are looking for this statement. The part in brackets is optional. If this is not the solution, please supply us with further information.

Upvotes: 0

Related Questions