zenguru
asked on
SQL to find number of occurrences of a word in a column
I have a table 'kws' in MySQL database:
id BIGINT(20) kw VARCHAR(255) terms VARCHAR(2048)
where id is the primary key
I need to find the number of rows in which the word contained in column kw is repeated 3 or more times in the column terms.
So what the raw SQL query for that?
kw contains the single word while terms contains multiple words that are delimited by ; ( it would be best if the query is not dependent on what type of delimiter is used). There are thousands of rows in the table.
Also what is the query to give all those rows in which column kw is repeated 2 or less times in the column terms. ( in this one not return the count but return the actual row data)
id BIGINT(20) kw VARCHAR(255) terms VARCHAR(2048)
where id is the primary key
I need to find the number of rows in which the word contained in column kw is repeated 3 or more times in the column terms.
So what the raw SQL query for that?
kw contains the single word while terms contains multiple words that are delimited by ; ( it would be best if the query is not dependent on what type of delimiter is used). There are thousands of rows in the table.
Also what is the query to give all those rows in which column kw is repeated 2 or less times in the column terms. ( in this one not return the count but return the actual row data)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
One last question that I should have asked in the original post:
I have 3 tables A, B, C which are related to each other. A has one to many relation with B and C also has one to many relation with B. B has only 3 columns id (primary key), a_id (foreign key from table A), c_id (foreign key from table C).
Now if I delete certain rows in table A that satisfies some condition in WHERE clause, would this automatically delete affected rows in tables B and C in order to maintain referential integrity? Or I have to do something in the SQL statement to ensure that?
All tables are innoDb tables in MySQL.