CODEWITHSUNDEEP
mysql
WildBill
WildBill

Reputation: 9291

How to tell if one string is contained within another text element in MySQL

I asked this question earlier:

How to get delta between two text items

But I also need a more binary answer. That is, is there a way to tell if one text entry contains another. Again the example:

This is a line.

and

This is a line.  And another.

Is there a simple way in MySQL to tell the first is contained in the second? Both of these items are entries in a column that is of type text.

UPDATE:

My table looks like this

id INT, mytext text

So, what I'd REALLY like to do is write a query that answers the following question:

Find all records in this table where mytext is contained in mytext_for_another_record where id=SOMENUMBER.

Maybe better put: Find all records where id=123 and mytext(for id 123) is contained in the record.

So I'm scanning the entire table for records that contain the text for the entry where id=123.

Example data:

id | text

1 | this is a line 2 | this is a line and here is another 3 | random 4 | random this is a line

Now, rather than short text entries, suppose the text was VERY long, like 1000 characters. Suppose I wanted to find all records in the database that contained the text that was in record 1. Is the only way to do so to say LIKE '%this is a line%' ? Is there a way to look by giving the id of the record?

Upvotes: 0

Views: 1394

Answers (4)

Thami Bouchnafa
Thami Bouchnafa

Reputation: 2167

you can also use concat():

SELECT id, searched,text FROM table_name WHERE text LIKE concat('%', searched, '%');

Upvotes: 0

hafichuk
hafichuk

Reputation: 10781

This should return you all of the lines containing the given text:

SELECT id, line FROM lines WHERE line LIKE '%This is a line.%';

The fiddle is here.

Upvotes: 0

JHS
JHS

Reputation: 7871

You could use the function LOCATE. If the 1 string is present in another you would get a number else 0.

The function is LOCATE(substr,str). For details have a look here

Upvotes: 0

Amadan
Amadan

Reputation: 198446

Check INSTR(haystack, needle) function, and compare it to 0. If 0, haystack does not contain needle. You could also do it with LIKE operator and wildcards (%), but you'd have to escape the % signs.

Upvotes: 2

Related Questions