SQL query to find with multiple conditions-CodePudding

looking for some help with simple SQL query. I need to fetch some results and manipulate them later, most of them based on the name. So I would like for example Select all Milk bottles not including any other milk products.

Example Table: Products

ID	Name	...
0	Milky Bar
1	Milk 2L
2	Milk 1L
3	Cadbury Dairy Milk Ice Cream
4	Other Dairy Milk Ice Cream

Query I used to fetch some items by word "Juice" for example:

SELECT *
FROM Products
WHERE Name 
LIKE '%Juice%';

CodePudding user response：

Operating under the assumption that all milk products names will be Milk <some number>L, you can use the following regex:

SELECT *
FROM Products
WHERE Name 
LIKE '^Milk [0-9] L$';

Note that this regex is extremely specific, if you deviate from the above assumption in the naming of the milk then it won't work. I suggest looking into common regex patterns if you'd like to expand what name entries you'd like to include!

CodePudding user response：

Based on your table I see no other option than something like this?

SELECT *
FROM Products
WHERE Name 
LIKE 'Milk %L';

CodePudding user response：

I am sorry guys, I think I could have very oversimplified table and the problem. Milk would be one of the issues but often times entries are written in many different ways. We've got plenty of the products and most of the time "Name" contains product name brand name which also is often times swapped by places.

So sometimes I will have:

ID	Name	...
...	Milk 1L Whole Müller
...	John Doe Farm 1L Milk
...	ICECREAMBRAND Milk Ice Cream

That's where the query LIKE '%Milk%' came to play originally. And here I would play with the SQL query to match exactly what I want to modify, specially there is tons of records.

But then again we've got some other suppliers, other products and they can even look like this:

ID	Name	...
...	KNORR Chicken Stock
...	KNORR Stock Veg
...	Beef Stock KNORR
...	KNORR Tomato Instant Soup
...	KNORR Instant Soup Leek & Potato
...	KNORR Seasoning Chicken
...	KNORR Potato Seasoning

Now I would like to grab all "KNORR" products related to specific category like KNORR Stock and do some changes to them.

So I was thinking if there is a way to do some query like:

SELECT *
FROM Products
WHERE Name
LIKE 'KNORR' AND 'Stock';

I know it is incorrect but if we could create SQL query which will be able to find those records this way I think that would solve all my issues. I am sorry I know I've got plenty to learn on SQL but honestly it's just that thing i need to modify. I do not work with SQL too often

CodePudding user response：

I feel dumb now. Not sure why I had a problem with it. So basically I have just put SQL Query like this:

SELECT *
FROM Products
WHERE Name LIKE '%KNORR%' AND Name LIKE '%Stock%';

Which seems to work fine for my needs...

In case I want to manipulate some entries and leave other I presume I could use:

SELECT *
FROM Products
WHERE Name LIKE '%KNORR%' AND NOT Name LIKE '%Stock%';

Dumb me... sorry for trouble guys

CodePudding user response：

If your Milk bottles always have the same type of name like "Milk " (Size), then it should be possible to just use the LIKE keyword in the following way:

SELECT *
FROM Products
WHERE Name LIKE 'Milk %L';

The LIKE Keyword has the functionality, that you can describe how the keyword should be positioned in the "Name" column.

If you write '%Milk%', then you will get all entries, that contain "Milk" at any position.

If you write '%Milk', you will get entries with the keyword 'Milk' in the end without any following characters.

For further information read this chapter: https://www.w3schools.com/SQL/sql_like.asp