CODEWITHSUNDEEP
sqlstringoracle-databasesearchconcatenation
user2313460
user2313460

Reputation: 11

Using strings from one column to search inside a string in another SQL

I am trying to search inside a sentence using the string from another column in SQL. I have a large list of keywords and their rating on a scale from 1 to 6. I am trying to search through data pulled down from Twitter to see what people thought of certain movies (i.e. Wreck It Ralph) and give the movie an aggregate score. The problem I am having is that the data pulled from Twitter isn't broken up, but still in a full sentence. I can't figure out a way to search through the sentence for the keyword. Here is a query that works:

SELECT SUM(K.Score) 
FROM wreck_it_ralph WIR, Keyword K
WHERE t_text LIKE '%fantastic%'
       AND K.word = 'fantastic';

Here is a query I tried using concatenation, but was unsuccessful:

SELECT SUM(K.Score) 
FROM Wreck_it_Ralph WIR, Keyword k
WHERE t_text LIKE ('%' + k.word + '%');

Upvotes: 1

Views: 4112

Answers (2)

Nishad
Nishad

Reputation: 426

MSSQL-:

SELECT SUM(K.Score) 
FROM Wreck_it_Ralph WIR, Keyword k
WHERE t_text LIKE '%' + k.word + '%';

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270873

Different databases have different ways of concatenating strings. In Oracle or Postgres, try this:

SELECT SUM(K.Score) 
FROM Wreck_it_Ralph WIR, Keyword k
WHERE t_text LIKE '%' || k.word || '%'

In SQL Server or Sybase:

SELECT SUM(K.Score) 
FROM Wreck_it_Ralph WIR, Keyword k
WHERE t_text LIKE '%' + k.word + '%'

And in MySQL:

SELECT SUM(K.Score) 
FROM Wreck_it_Ralph WIR, Keyword k
WHERE t_text LIKE concat('%', k.word, '%')

Do note that you have a problem with partial word matches. So, something "dislike" will match "like". You may want to look into the full text capabilities of whichever database you are using, because that takes into account punctuation, full words, and stop-lists.

Upvotes: 1

Related Questions