0

I didn't found a solution for my problem.

Here's a example:

A have a Table, for example: Person. And it has a column that contains alot number separated by ",". These numbers represent things, but it isn't relevant right now.

Table Person:

nm_name: Jonas
nm_email: jonas@example.com
ds_numbers: 1,2,3,5,17,27,67,77,

I want to make a query that filter the searchs in table person by this column ds_numbers. But the user will select witch numbers will by the filter, for example, if user select at least 1 number that is in this column, the register will come.

If I select, for example, "3" this register will come, or "3,55,12" will come too, because at least 1 value need to be in string.

The problem it, I can't use Like '%4%' because I would return 4, 41, 74... Any different solutions?

Important: I mount this query in Java, so I can use Java features, like Loops, but I didn't found a solution. Any ideas?

And I'm using MySQL.

Termininja
  • 6,620
  • 12
  • 48
  • 49
Jonas F
  • 17
  • 4
  • Possible duplicate of [Can you split/explode a field in a MySQL query?](http://stackoverflow.com/questions/471914/can-you-split-explode-a-field-in-a-mysql-query) – Jasha Aug 12 '16 at 13:44

2 Answers2

0

You might be able to get away with a simple text search using wildcards with the LIKE operator.

SELECT ds_numbers FROM table WHERE ds_numbers LIKE '%3%'

If not then you can try the fulltext-search capabilities as mentioned in the answer for this question: Search for string within text column in MySQL

Community
  • 1
  • 1
Sean Wang
  • 778
  • 5
  • 14
0

You can use LIKE, but with the current format, you will get false positives. '%4%' will also match the number 40, or 14.

Ideally, change your data to store the numbers with a delimiter. "|3|,|55|,|32|". Then when you select using LIKE '%|3|%', it will only match the |3|.

You could use LIKE '%3,%', but that won't match the last number if 3 is the very last number in the string.

SilicaGel
  • 459
  • 3
  • 11
  • I thought about it, to use '%', it really would not work. But I liked your solution to use the "|". Sure would work but have a giant base already populated, it is not feasible. But thanks for sharing! – Jonas F Aug 12 '16 at 13:47
  • 1
    Do your fields have a trailing comma at the end? (in your example, you have one, but it may just be a snippet.) If not, can you add a trailing comma to the fields? if so, then you could use LIKE '%,3,%' or LIKE '%,343,%' – SilicaGel Aug 12 '16 at 14:13