Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote -1 down vote favorite

If I have a column with data type varchar(max) in a table that contains a big piece of text, can I make a connection between a word or a sentence in that text with another word or a sentence in another column with data type varchar(max) in different table?

For example, if I wrote a word in the first column, can I retrieve all sentences that it's mentioned in in another column?

enter image description here

share|improve this question
1  
Hello Hatem, your question isn't very clear. Could you provide for example two or three sample rows, with sample text, and an example of how you would want to be able to query them? (Doesn't have to be proper SQL, just something so that people can understand exactly what you're trying to do). – Mat Oct 31 '12 at 9:06
@Mat i will ask a new question with examples – Hatem Ghazy Oct 31 '12 at 9:16
1  
@Hatem: you should not do that. Edit this one and add the details. There's an "edit" link right under the tags, please use that. – Mat Oct 31 '12 at 9:33
@Mat is it clear now – Hatem Ghazy Oct 31 '12 at 9:44

1 Answer

up vote 3 down vote accepted

Warning. This will run badly because of the leading wildcard search in plain SQL

SELECT DISTINCT
   T2.BigColumn
FROM
   table1 T1
   JOIN
   table1 T2 ON T2.BigColumn LIKE '%' + T1.KeyWordColumn + '%'

If your database engine supports full text search, then this is the way to go.

share|improve this answer
1  
+1 Hopefully this is a one time activity to generate a linking table between drugs and the drugs they interact with. – Leigh Riffel Oct 31 '12 at 13:00
Maybe an extra warning or two about case-sensitivity (might depend on dbms & collation?) and the fact that that won't match whole words only (i.e. a query for RNA's "technical name" would also find DNA entries - no idea if that's relevant to the domain though). – Mat Oct 31 '12 at 13:28
@Mat: hopefully comments will be read too. I'm inclined to apply KISS for now... – gbn Oct 31 '12 at 13:30

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.