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

Question

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 + '%');

SELECT SUM(K.Score) FROM Wreck_it_Ralph WIR, Keyword k WHERE t_text LIKE '%' + k.word + '%'; — realnumber3012
– realnumber3012, Commented Apr 24, 2013 at 0:46
The concatenated query seems like it would work. What database server are you using? — gmm
– gmm, Commented Apr 24, 2013 at 0:48
I fixed the query so it's WHERE WIR.t_text LIKE ('%' + k.word + '%') but now I get back an error that says "invalid number" — user2313460
– user2313460, Commented Apr 24, 2013 at 0:52

Gordon Linoff · Accepted Answer · 2013-04-24 00:58:00Z

1

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.

answered Apr 24, 2013 at 0:58

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

user2313460 Over a year ago

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

user2313460 Over a year ago

THANK YOU! I don't understand what these || symbols are doing though

Gordon Linoff Over a year ago

@user2313460 . . . That happens to be the Oracle syntax for string concatenation.

Nishad · Accepted Answer · 2013-04-24 06:51:34Z

0

MSSQL-:

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

answered Apr 24, 2013 at 6:51

Nishad

4262 gold badges7 silver badges20 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

3 Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Related