1

I have a text column ( MISCDATA ) in a database wich contains multiple numeric values and string values, separated by comas and semicolons. Is there a way to build a query to select only the rows where MISCDATA contains a number bigger than 50 (example)?

I thought of the use of FIND_IN_SET() but I can't place it in the correct context.

SELECT * FROM Mytable WHERE FIND_IN_SET('NUMBER BIGGER THAN 50', MISCDATA);

I don't know if I explained myself correctly, anyhow all help will be apreciated. Thanks.

EDIT:

Some example data from the field:

MA, 22; HR, 42; HG, 29; JW, 44; MI, 76; GJ, 56;

The above example should be listed by the SELECT because it contains 2 numbers bigger than 50 (MI and GJ).

user3529213
  • 73
  • 1
  • 8
  • 1
    If you're in the position to change the database design, by all means do it. Never store multiple values in one column. Read about normalization. – fancyPants Jul 24 '14 at 09:34
  • I can imagine such solution. But it is near senseless. You have very clear intention - to work with separate values. So store them as separate entities. 1:many relation (so additional table) must be applied for your database design. – Alma Do Jul 24 '14 at 09:51
  • I am not in the position to do that. I am barely a PHP consultant. Also, I wouldn't know to what other type I would change it to. – user3529213 Jul 24 '14 at 09:53
  • Is each numeric value exactly two digits? For example, is "MA, 125" a possible value? How about "MA, 6"? – David Gorsline Jul 24 '14 at 12:41
  • The values are from 0 to 99. Never bigger than 99. And also, the number could not exist, leaving the field like "GJ, ;" – user3529213 Jul 28 '14 at 08:00

1 Answers1

-1

A solution would be to use REGEXP : 

SELECT * FROM Mytable WHERE REGEXP '@yourRegexToWrite'.

The job is to write a good regex...

RiggsFolly
  • 93,638
  • 21
  • 103
  • 149
tacou_st
  • 109
  • 1
  • 9
  • A helpful answer would include the regular expression that solves the questioner's problem. Also, the SQL in this answer is not good syntax: there's no column name in the WHERE clause. – David Gorsline Jul 24 '14 at 10:39
  • Well actually, I cannot leave a comment to this post, I could only post an answer. As you say, this is not a complete answer, but maybe the way to get it. I thought this would help a little... – tacou_st Jul 24 '14 at 10:48