MySQL fetch the rows, which wont contain only the specific string / word

Question

I want a MySQL query to return rows which don't have the only input string/phrase in it.

Example: I have table with the column name like

Sno  | sname
---------------
1    | AAA
2    | SSS
3    | Group Post
4    | Group Post, Test Run
5    | Group Post, Group Post
6    | Group Post, Group Post, My test Run

In the above table I want to eliminate the rows which have "Group Post" only in them. That means how many times Group Post exist in the row and there are no other phrases or words in them, then we can eliminate it.

So I want a query which will return

Sno  | sname
---------------
1    | AAA
2    | SSS
4    | Group Post, Test Run
6    | Group Post, Group Post, My test Run

In these rows some of them have Group Post in them. But along with the that we have other phrases in them.

Thanks in Advance.

Sorry, but your question is very hard to decipher as written. Do you want rows 3 to be eliminated? Or more than that? Please provide an "after" example to go with your "before" example, I'm sure that will help clarify what you're looking to do. — Kzqai
– Kzqai, Commented Feb 14, 2014 at 11:35
Why can't you try it in php by getting all the row values and check it store which values were required — krishna
– krishna, Commented Feb 14, 2014 at 11:44
I'd use PHP, get the values in an array and explode the sname - go through the resulting array and pick and choose which I keep or throw out — wribit
– wribit, Commented Feb 14, 2014 at 11:49
@Kzqai: I want to fetch only the results like you edited in the post. 1,2,4,6. — Kiran
– Kiran, Commented Feb 14, 2014 at 12:07

Ravinder Reddy · Accepted Answer · 2014-02-14 11:50:14Z

5

You need REPLACE function to filter the data.

Try this:

select * from my_table
 where length( replace( replace( sname, 'Group Post', '' ), ', ', '' ) ) > 0

Refer to:
MySQL: REPLACE(str,from_str,to_str) Function

answered Feb 14, 2014 at 11:50

Ravinder Reddy

24.1k6 gold badges54 silver badges86 bronze badges

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

1 Comment

Kiran Over a year ago

Hi Ravinder, Thanks for the Answer. Your Query Solved my problem.

Viswanath Polaki · Accepted Answer · 2014-02-14 11:52:27Z

0

try a subquery for finding all the ids which have the string and then use not in for example

SELECT * FORM {table_name} where id not in (select * from {table_name} where sname = "YOUR_STRING");

edited Feb 14, 2014 at 11:52

answered Feb 14, 2014 at 11:47

Viswanath Polaki

1,4241 gold badge12 silver badges23 bronze badges

1 Comment

krishna Over a year ago

This will not solve the OP problem. Plz read the question clearly

abhishek · Accepted Answer · 2014-02-14 11:59:52Z

0

select Sno,sname from table_name where length(replace(sname,'Group Post','')) > 0;

answered Feb 14, 2014 at 11:59

abhishek

375 bronze badges

Comments

Ashish Jagtap · Accepted Answer · 2014-02-14 12:00:28Z

0

QUERY

SELECT *
FROM test
WHERE sname NOT LIKE '%Group Post'

SQL FIDDLE DEMO

hope this will help you...!

answered Feb 14, 2014 at 12:00

Ashish Jagtap

2,8193 gold badges33 silver badges46 bronze badges

Collectives™ on Stack Overflow

MySQL fetch the rows, which wont contain only the specific string / word

4 Answers 4

1 Comment

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related