Search code examples
mysqlsqlselectrecordssql-like

Sql - can I search for particular numbers in a record?

I have a column in a table that contains data like:

column: col_name
 row 1: 3451,3547,234,456,6487,2341,7856,546,76
 row 2: 4746,234,6757,234,7657,6346,563
 row 3: 546,7467,4562,456,234
 row 4: 563,3246,5641,4637,234,7854,567,577

I would like to be able to select a record based on whether it has a certain number in it.

E.g. as 234 appears in all rows, it selects them all; 563 appears in 2 and so on. This does not work:

select col_name from table_name where col_name like '%234%';

which I think may be because I'm confusing how I should be selecting columns and rows, but I'm at a blank for what else could work, help appreciated!

Edit: I think I'm confusing myself and some of the repliers (sorry, folks). I'm not trying to find the column name that matches my query, I'm trying to find data in the column that matches my query, aka data in the rows. Some of the answers seem geared towards an unknown column name, but I know which column I'm using.

This is what I would expect to get if I search for a row with 563 in it:

4746,234,6757,234,7657,6346,563      (row 2)
563,3246,5641,4637,234,7854,567,577  (row 4)

If I search for it using all the values in the query like so:

select col_name from table_name where col_name = '4746,234,6757,234,7657,6346,563';

then it will return the row: 

4746,234,6757,234,7657,6346,563      (row 2)

but I only want to be able to search for it using one number, not several, as they represent different things.

Solution

  • You can do this using FIND_IN_SET:

    SELECT col_name 
FROM table_name 
WHERE FIND_IN_SET('563', col_name);

    Example: SQLFiddle