I have a database field called willingness populated with text like:
shopping,pool,pc,games
shopping,pool,pc,games
swimming, pool, pc, games
I need sql query that queries the fileds which have "shopping" inside?
TY
-
what was the reason to store the data in such unusable form?zerkms– zerkms2010-11-13 08:54:21 +00:00Commented Nov 13, 2010 at 8:54
-
Its data from rss field, wasn't made by mewebmasters– webmasters2010-11-13 09:04:29 +00:00Commented Nov 13, 2010 at 9:04
-
1So then split it up when you parse the feed.Ignacio Vazquez-Abrams– Ignacio Vazquez-Abrams2010-11-13 09:05:28 +00:00Commented Nov 13, 2010 at 9:05
Add a comment
|
7 Answers
Select * from table1 where willingness like '%shopping%'
Comments
Very inefficient, as this really should be normalized, but should work:
SELECT *
FROM table
WHERE willingness LIKE '%shopping%'
In a normalized database, you would have a willingness table with an ID for each (say 1 for shopping) and a foreign key to this lookup table. The query then would be much more efficient:
SELECT *
FROM table
WHERE willingness_id = 1
Comments
This is why we normalize.
AND (willingness = 'shopping'
OR willingness like 'shopping,%'
OR willingness like '%,shopping'
OR willingness like '%,shopping,%'
OR willingness like '%, shopping'
OR willingness like '%, shopping,%')
answered Nov 13, 2010 at 8:55
Ignacio Vazquez-Abrams
804k160 gold badges1.4k silver badges1.4k bronze badges
Comments
You can use the LIKE operator for this purpose. For example,
SELECT * FROM myTable
WHERE willingness LIKE '%shopping%'
However, if the field is simply storing a comma-separated list of tags, you might want to change the schema instead - as it stands, the schema is not normalized. For example, the table could have UserId and WillingnessTagId columns with the appropriate foreign-keys.
Comments
that should be enough:
select *
from table
where willingness like '%shopping%'
this will work in sql server
Comments
This should do the JOB :
select willingness from table where willingness LIKE '%shopping%'
I recommands you to read about string comparison in MYSQL statement : http://dev.mysql.com/doc/refman/5.0/fr/string-comparison-functions.html
I assume you are using MySQL, if not please tell us what you are using.
Comments
You should NOT store the data in your database like this. Let me explain:
You can use the solution which people mention here (like %shopping% etc), but you're literally asking for trouble this way.
Read about normalization. Instead of a willingness field, you could have a willingness table which would look somehow like this:
person_id willing
1 pool
2 shopping
2 pool
3 swimming
3 games
Etc.
Then you only need a query like select distinct person_id from willingness where willing = "pool". That's it - much safer and probably faster.
