I need something like this:
SELECT * FROM `table` WHERE '123' IN (`column`)
In 'column' (varchar 255 field) there are some numbers like 1, 50, 145, 123, 58,
I don't know to explain better, but I think you understand what I need.
-
3find_in_set('123',column) (dev.mysql.com/doc/refman/5.6/en/…) or normalise your data properlyMark Baker– Mark Baker2013-04-22 11:32:17 +00:00Commented Apr 22, 2013 at 11:32
-
post what you have tried??Pathik Gandhi– Pathik Gandhi2013-04-22 11:32:20 +00:00Commented Apr 22, 2013 at 11:32
-
Are the numbers 1,50,145... one-per-row? Or is the 'column' field in each row a string containing all the numbers?Osiris– Osiris2013-04-22 11:32:55 +00:00Commented Apr 22, 2013 at 11:32
-
@Osiris, numbers are in one row, 'column` field containing all these numbers.Alexander– Alexander2013-04-22 11:35:44 +00:00Commented Apr 22, 2013 at 11:35
Add a comment
|
4 Answers
SELECT * FROM table WHERE FIND_IN_SET('123', column);
See http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_find-in-set
3 Comments
Alexander
Thank you, but works fine only if I search first number on all these numbers. I have all these numbers in one field (name here 'column'). These numbers are in one row, column = 1, 50, 145, 123, 58, in this example.
karmafunk
To clarify, your field 'column' is "1,50,145,123,58". You are searching this column for the occurrence of X which could be any of these numbers right?
karmafunk
You field 'column' needs to be of type SET.
2 Comments
Alexander
Thank you, but works fine only if I search first number on all these numbers.
alwaysLearn
@Bogdan have you tried it .. this should irrespective of position
SELECT * FROM table WHERE column LIKE '%123%';
2 Comments
Alexander
Thank you, but for example if I search 123 but there is a number like 1234, will return wrong result, I need to search exactly for 123 number between all these numbers
ndasusers
Oh. Then if you cannot use the other SET suggestion, you must split the
column result somewhere along the line and test them. If you use LIKE to return a set with some extra rows, your post processing may be easy with PHP, or you will have to write a function in MySQL to do that for your. I'm sorry but I have not time to make it now. Here is a fine beginning example.[blog.fedecarg.com/2009/02/22/mysql-split-string-function/]try this:
SELECT * FROM table WHERE column IN ('123');
