CODEWITHSUNDEEP
mysqlsql
maco1717
maco1717

Reputation: 843

SQL WHERE LIKE OR FILTER

I have this query

SELECT DISTINCT publishedapp
FROM tbl_name
WHERE publishedapp LIKE "%@%"
OR publishedapp LIKE "%Desk%"
OR publishedapp LIKE "%RDP%"
OR publishedapp LIKE "%CTX%\"
ORDER BY publishedapp

It returns a list but from that list I want to filter a couple entries, Ive tried adding AND publishedapp NOT LIKE \"%ActiveH Desktop June 2013%\" like this

SELECT DISTINCT publishedapp
FROM tbl_name
WHERE publishedapp LIKE "%@%"
OR publishedapp LIKE "%Desk%"
OR publishedapp LIKE "%RDP%"
OR publishedapp LIKE "%CTX%"
AND publishedapp NOT LIKE "%ActiveH Desktop June 2013%"
ORDER BY publishedapp

but it doesnt exclude "ActiveH Desktop June 2013" from the list. Any ideas how can i selectively take out row from a filtered list?

From this table

Username    Client Name Date    Time    Published App
abim     009283-LAP  01/08/2015  19:18:40.90     Mencap@Work2-1
adetolaok    005421-DSK  01/08/2015  15:14:24.51     Mencap@Work2-1
amandawo     AMANDA-FIXED-PC     01/08/2015   9:20:29.01     Mencap@Work2-1
amandawo     009759-DSK  01/08/2015  11:15:14.18     Mencap@Work2-1
AndreasR     015029-LAP  01/08/2015  16:17:08.15     Mencap@Work2-1
AnneG    009255-LAP  01/08/2015   8:36:16.91     Mencap Desktop with Acrobat
AnneG    009255-LAP  01/08/2015  10:27:40.10     Mencap Desktop with Acrobat
AnneG    009255-LAP  01/08/2015  11:32:57.52     Mencap Desktop with Acrobat
AntonyT  ANTONY  01/08/2015  11:22:10.08     Mencap@Work2-1
assend   XL3SS   01/08/2015  12:02:30.32     Desktop on NC-CITRIXIT01
BrianW   BRIAN-HP    01/08/2015  19:00:00.02     Mencap Desktop with Office 2010
CandiceL     010198-LAP  01/08/2015  21:05:40.67     Mencap@Work2-1
carolinej    009132-LAP  01/08/2015  14:52:02.40     Mencap Desktop with Acrobat
CharlotteWi  015084-DSK  01/08/2015  16:09:17.25     Mencap@Work2-1
ChelseaS     005240-LAP  01/08/2015  11:15:11.69     Mencap@Work2-1
chrisch  CHRIS-PC    01/08/2015   8:11:42.06     Powerplan
Ciaram   008615-LAP  01/08/2015   8:46:31.71     Mencap@Work2-1
ClaireTu     009588-DSK  01/08/2015  11:40:15.15     Mencap @ Work Desktop
clemmiet     008956-LAP  01/08/2015  21:17:45.47     Mencap Desktop with Office 2010
ColetteP     009363-LAP  01/08/2015   9:36:48.10     Mencap@Work2-1
danielleba   009723-DSK  01/08/2015  13:40:36.72     Mencap@Work2-1
danielleba   009723-DSK  01/08/2015  13:41:01.34     Mencap@Work2-1
danielleyo   004425-DSK  01/08/2015  19:46:38.96     Mencap @ Work Desktop
darrenp  015148-DSK  01/08/2015  12:05:03.50     Mencap@Work2-1
davidf   roid37e2c5c861b3993     01/08/2015  21:43:51.36     Mencap@Work2-1
davidpar     004451-DSK  01/08/2015   8:48:57.15     Mencap@Work2-1
dawnpo   009359-LAP  01/08/2015  12:50:37.28     Thin Client Desktop
deboraho     007019-LAP  01/08/2015  15:25:38.81     Mencap@Work2-1
debradu  009410-LAP  01/08/2015  18:11:43.92     Mencap @ Work Desktop
deen     010336-LAP  01/08/2015  10:50:40.99     Mencap@Work2-1
dhumisaniM   007019-LAP  01/08/2015   8:22:30.58     Mencap@Work2-1
dianeh   NICKS   01/08/2015  11:03:26.73     Mencap@Work2-1
dianeh   NICKS   01/08/2015  11:05:26.17     Mencap@Work2-1
Eileenh  009786-LAP  01/08/2015  10:02:20.25     Mencap@Work2-1
Gabby    008518-LAP  01/08/2015  19:55:49.40     Mencap@Work2-1
GaliniP  005703-DSK  01/08/2015  22:19:34.59     Mencap@Work2-1
garypl   005635-DSK  01/08/2015  10:40:43.18     Mencap@Work2-1
gillt    GILLSCOMPUTER   01/08/2015  21:36:55.34     Mencap@Work2-1
h2005    005359-DSK  01/08/2015   8:02:32.38     Mencap@Work2-1
h2033    009434-DSK  01/08/2015  16:43:40.31     Internet Explorer
h2033    009434-DSK  01/08/2015  22:06:46.48     Internet Explorer
h2053    009216-DSK  01/08/2015  14:31:46.27     Mencap@Work2-1
h2087    005618-RMS  01/08/2015  12:37:57.20     Internet Explorer
h3012    009723-DSK  01/08/2015   7:24:28.99     Internet Explorer
h3012    009723-DSK  01/08/2015   7:24:29.16     Internet Explorer
misactive    AMSDSSMORRIS    16/07/2015  11:06:42.95     ActiveH Desktop June 2013

id like to get back

 Mencap@Work2-1
 Mencap Desktop with Acrobat
 Desktop on NC-CITRIXIT01
 Mencap Desktop with Office 2010
 Mencap @ Work Desktop
 Thin Client Desktop

Upvotes: 0

Views: 99

Answers (3)

manish kumar
manish kumar

Reputation: 26

Just use all the OR conditions in bracket.

SELECT DISTINCT publishedapp
FROM tbl_name
WHERE (publishedapp LIKE '%@%'
OR publishedapp LIKE '%Desk%'
OR publishedapp LIKE '%RDP%'
OR publishedapp LIKE '%CTX%')
AND publishedapp NOT LIKE '%ActiveH Desktop June 2013%'
ORDER BY publishedapp

Here the problem is that the last statement is true but none of the other conditions are. So because of 'AND' the final result of the where condition is false and it is not filtering 'ActiveH Desktop June 2013'.

Upvotes: 1

Strawberry
Strawberry

Reputation: 33945

SELECT * FROM strings WHERE string REGEXP '@|RD|DP' LIMIT 100;
+-------+--------+-------+
| id    | string | i     |
+-------+--------+-------+
|    20 | DP     |   229 |
| 22873 | RD     |   587 |
| 24353 | DP     |   392 |
| 32773 | RDQ    | 13558 |
| 32774 | XI@    | 17104 |
| 32780 | RDT    |  5572 |
| 32790 | I@Y    | 13073 |
| 32831 | OD@    | 10401 |
| 32834 | BG@    |  5673 |
| 32857 | @HM    | 10159 |
| 32879 | Q@D    | 13545 |
| 32890 | RP@    |  4506 |
| 32900 | @XR    | 10049 |
| 32908 | J@Y    | 12335 |
| 32914 | RDR    | 15692 |
| 32920 | F@H    |  9015 |
| 32969 | ADP    | 10103 |
| 33014 | @MN    |  3440 |
| 33018 | KDP    |  9001 |
| 33032 | @U@    | 11459 |
| 33074 | MR@    | 16191 |
| 33077 | @RM    |  7167 |
| 33127 | @TV    | 15944 |
| 33301 | @DT    |  7376 |
| 33303 | H@D    | 12083 |
| 33441 | QZ@    |  1414 |
| 33445 | @KA    | 16389 |
| 33448 | RDT    |  6724 |
| 33524 | IP@    |  4508 |
+-------+--------+-------+

Upvotes: 0

Shammas
Shammas

Reputation: 461

try the below code

SELECT DISTINCT publishedapp
FROM tbl_name
WHERE (publishedapp LIKE '%@%'
OR publishedapp LIKE '%Desk%'
OR publishedapp LIKE '%RDP%'
OR publishedapp LIKE '%CTX%')
ORDER BY publishedapp

Upvotes: 0

Related Questions