Find text on server: numeric fields and ranges

[expired user #9861]'s profile image [expired user #9861] posted 8 years ago in General Permalink

From time to time I have to search for numbers in a database.

The (lifesaving) "Find text on server" function is great, but it's very limited when working with numeric fields: what about adding options to look for numbers with this options?

  • smaller than X
  • smaller or equal to X
  • equal to X
  • bigger or equal to X
  • bigger than X
  • between X and Y (inclusive)
  • between X and Y (exclusive)

If this will be implemented, a handy option could be to limit also the search in integer/floating point/both fields.

PS: bacause of this change, I'd rename "Find text on server" to "Find on server" and "Find text" to simply "Find"

PPS: thanks!!!

ansgar's profile image ansgar posted 8 years ago Permalink

Please look into this thread, for which I have just committed r5083. HeidiSQL now supports various wildcard options in that dialog. At least that solves your "equal to x" wish for now.

[expired user #9861]'s profile image [expired user #9861] posted 8 years ago Permalink

Yes, I saw that commit, and that's really great.

But I think that searching for numbers could be a bit better.

Here's what I'd do in the "Find text" tab of the "Table tools" dialog:

  1. Move the "Search in column types:" to top, and replace it with a combo box like the one you see in the first attachment (1-data-type.png).

  2. If the selected value is not numeric, keep the current "Text to find" and "Match type" fields

  3. If the selected data type is numeric, hide "Text to find" and "Match type" and show something like what I badly tried to represent in the second attachment (2-search-numbers.png). That new fields allow searching for numbers comparing against one value (eg "gimme numbers greater than 3") or two values (eg "gimme numbers greater than 3 and less or equal to 5")

2 attachment(s):
  • 1-data-type
  • 2-search-numbers
ansgar's profile image ansgar posted 8 years ago Permalink

What about adding a checkbox saying "Use above text as SQL comparison", and then support such a syntax in the text field:

%column > 10 AND %column = 20

... or even for strings:

%column = MD5('123') OR %column LIKE '%:%'

The checkbox for "Case sensitive" should then be disable of course, as that makes no sense in such cases.

[expired user #9861]'s profile image [expired user #9861] posted 8 years ago Permalink

Yes, that could be a very powerful option!

What about adding to the "Match type" combobox an option like "advanced - custom SQL" that enables this behavior?

ansgar's profile image ansgar posted 8 years ago Permalink

Sounds good. That way we don't waste space there, and the options are mutually exclusive anyway.

Please login to leave a reply, or register at first.