SQLite, select where field 'like' field from another table

amateurjustin Published at Dev

amateurjustin

I'm trying to remove "bad words" from a table using a list of offensive words from another table, but the offensive words are often in lemma form. Thus, I can't always do a exact match. I need to use a LIKE quantifier, for example WHERE text LIKE '%badword%'

Is there a way for me to try to select all the rows that contains a word from another table, but by specifically using the LIKE clause.

I'll give an example of what I was trying to do. This didn't work, but should give you an idea of what I'm trying to do:

SELECT *
FROM entry 
WHERE headword LIKE IN
( SELECT word
FROM sies )

Now I know that the LIKE doesn't fit into this query, but I'm trying to get something to this effect working.

EDIT: This is the latest thing I tried, that did not work:

SELECT s.*
FROM sies AS s, entry AS e 
WHERE e.headword LIKE ('%' + s.word + '%')

Prerak Sola

You can try the following query:

select s.* from sies s, entry e where s.words like '%' || e.word || '%'

Here I have assumed that, sies is your regular table and entry is the table with the dirty words.

You can check the Demo here.

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at2021-02-18

Comments

0 comments

From Dev

Related Related

Article

SQLite, select where field 'like' field from another table

SQLite, select where field 'like' field from another table

select multiple columns from another table where field contains array

select field from table in another field from another table

SQLite select and count from another table with like

SQLite select average from one table and update average field from another table using trigger

Select from Table Where field equals a variable

Select from mysql table WHERE field in '$array'?

Select Additional Field from another Table

Call field from another table in select firebase

Select all fields from a table where a field in another table with an ID is equal to a string

SELECT * FROM table WHERE "substring" IN table_field?

Select field from table A where subselect from table B = table A field

BigQuery: select from regexp_match where field in other table

Dynamic table name in where clause using a field from select

Select all columns from table where one field is duplicated

Laravel select random rows from table based on another field

why if($row['field'] == value) work like SELECT WHERE field = value?

select (sum(field) - max(field)) from table group by field

SELECT where when Max of one field corresponding field in another is something

select values of one field where another field equals a value in rails

Select (Select field from FieldTable) from Table

MYSQL - Select all rows in a table where it's ids in another table as string field

MySQL - How to select rows in a table where id value is in a comma delimited field in another table?

select a field values that are not found in another table

select a field values that are not found in another table

SQL, select field if field doesn't exist in another table

SQL, select field if field doesn't exist in another table

How to create new field in view with some offset (field of table) from existing field (another field of table)?

Fastest way to count from a table where a certain field exists in another table?

Select from JSONB field with WHERE clause