mysql select value where contains string but not with space after
I'm really stuck on something which is probably really easy...so apologies in advance!
I have a column which can contain any of the following for instance:
Thin
or
Super Thin
or a set of values with pipe delimiters
Thin||Super Thin||Other Value||Another Value
I want to select only occurrences of Thin but not Super Thin
I've tried LIKE AND NOT LIKE but this doesn't seem to work:
SELECT value FROM table_name WHERE value LIKE "%Thin%" AND value NOT LIKE "%Super Thin%"
Also looked into REGEXP but not sure how to use in this case.
many thanks
I have a column which can contain any of the following for instance:
Thin
or
Super Thin
or a set of values with pipe delimiters
Thin||Super Thin||Other Value||Another Value
I want to select only occurrences of Thin but not Super Thin
I've tried LIKE AND NOT LIKE but this doesn't seem to work:
SELECT value FROM table_name WHERE value LIKE "%Thin%" AND value NOT LIKE "%Super Thin%"
Also looked into REGEXP but not sure how to use in this case.
many thanks
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 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.
or also working as this way
--thin list
SELECT value
FROM table_name
WHERE value <>"Supper Thin"
--supper thin list
SELECT value
FROM table_name
WHERE value ="Supper Thin"
--thin list
SELECT value
FROM table_name
WHERE value <>"Supper Thin"
--supper thin list
SELECT value
FROM table_name
WHERE value ="Supper Thin"
ASKER
I can see that it all makes sense and should be working, but it doesn't work in my instance - I'm baffled - here's the details of a query I've tried:
tablename contents
id: 145792
otherid: 20
contentid: 6536
value: Glass||CR39||NXT||Thin||Su
sql query:
SELECT value FROM tablename WHERE value LIKE "%Thin%" AND value NOT LIKE "%Super Thin%" AND contentid = 6536 AND otherid = 20
but result is nothing.
sql query:
SELECT value FROM modx_site_tmplvar_contentv
gives me result:
Glass||CR39||NXT||Thin||Su
as expected
tablename contents
id: 145792
otherid: 20
contentid: 6536
value: Glass||CR39||NXT||Thin||Su
sql query:
SELECT value FROM tablename WHERE value LIKE "%Thin%" AND value NOT LIKE "%Super Thin%" AND contentid = 6536 AND otherid = 20
but result is nothing.
sql query:
SELECT value FROM modx_site_tmplvar_contentv
gives me result:
Glass||CR39||NXT||Thin||Su
as expected
ASKER
sorry I left the actual table names etc in the last one by mistake just to confuse things even more!
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
thanks for your help all - I have sussed out what my problem was, partly due to me not understanding how the LIKE returns the result and partly as I had both logos showing in both cases!
I'll share the points as all comments have been correct!
I'll share the points as all comments have been correct!
ASKER
the problem is Thin is within both values (Thin and Super Thin) so I need to find a way to make sure it's just finding Thin
hope that makes sense..
thanks