0

I'm currently a bit stumped on how to implement a certain thing. Basically I have an embedded database(which really hates JOINs, performance-wise) and the requirement of adding a wildcard mechanism to a search field. Currently the search field allows the User to input a date and some amounts/revenues which is matched using something like: SELECT * FROM table where date >= '{0}' AND date <= '{1}'

EDIT: This is due to the fact that a .NET adapter generates the SQL at runtime with various combinations of AND-clauses being possible. Could be the user inputs only a value for {0}, could be he only inputs one for {1}, could be he inputs both, could be he inputs none. Therefore BETWEEN sadly would fall flat unless I start some serious regexing.

I would like to use the keyword LIKE to implement something like:

SELECT * FROM table where date >= LIKE '{0}' AND date <= LIKE '{1}' with {0} being something like "2015.01*"

I recognize that Date might be a bit much, but using stuff like amounts or revenues is this possible? If so, what's the right syntax? Google is not my friend on this...

Thanks for all your help in advance!

fk2
  • 739
  • 1
  • 14
  • 30
  • Why dont you use `between` clause? `select * from something where abc between 0 and 5`. – Amnesh Goel Jun 11 '15 at 15:20
  • See Edit for why it would be hard to do that? Also how would that query look? `SELECT * FROM table WHERE date BETWEEN LIKE {0} AND LIKE {1}` ? – fk2 Jun 11 '15 at 15:27
  • Well if you are writing your query at runtime in .NET then you have all options with you. Since you know that you may have one or two parameters to compare with, then best case for single comparison is to `=` when you are sure about the value, use `LIKE` when you want to do a matching pattern and use `BETWEEN` when you have two values like `between 10 and 50`. – Amnesh Goel Jun 11 '15 at 15:29
  • Choose your options wisely, because each way has its +ve and -ve side. Don't use `LIKE` if you do not have any pattern to match. If you are getting direct value then use `=`. Don't forget you have freedom to use other comparison operators like `>=`, `<=` and `!=` etc. – Amnesh Goel Jun 11 '15 at 15:31
  • Right, but how would the query look if I want to do both a value comparison and pattern matching at the same time? Or is that just not possible? – fk2 Jun 11 '15 at 15:34
  • That is possible. I'm sure at query building time you must be knowing what type of query you want to build. So prepare a logic in .NET accordingly. Like `If {1} = string.empty then select * from abc where a = {0} or a like '{0}%' else select * from abc where a between {0} and {1}` or more variants are possible.. let me know what are you building – Amnesh Goel Jun 11 '15 at 15:37

1 Answers1

3

LIKE is doing a regular expression matching, so for example LIKE '%a' matches '1a', but also '2a'. From this it is quite clear that a >= LIKE '...' is not possible.

What you could do is build a string from the date, so that you have for example 'YYYYMMDD', then you could easily compare with LIKE 'YYYYMM%' or with >= 'YYYYMM'.

Still I do not understand why you don't just use the SELECT * FROM table where date >= '{0}' AND date <= '{1}' (or a variant of that query using between) that you already proposed.

Werner Henze
  • 16,404
  • 12
  • 44
  • 69
  • The value in {0} has to be regular expression matchable, meaning a user should be able to input "1234%" or "1234*" (with some string replace) and the query I'm dynamically building should really search for that matching – fk2 Jun 11 '15 at 15:31
  • @fk2 If you really want regex matching, than use the approach I said (YYYYMMDD formatted column, LIKE ...). If you could narrow down the user input to always be YYYY* or YYYYMM* you could calculate a min/max date for <= and >= comparison. That would perform much better. – Werner Henze Jun 11 '15 at 15:42
  • Yeah, that would probably work for a Date. I was hoping for an easier way but I guess I'll bite the bullet. How would that work if I had a non-formatted input like an amount, which could be any length? – fk2 Jun 11 '15 at 15:51
  • @fk2 What exactly do you mean? A column amount of type int and comparing against a string '123*'? Would be quite easy: just format the int as a string, than you can LIKE. But would it make sense to compare a number against '123*' matching 123, 1234, 1235, 123000000, ...? – Werner Henze Jun 11 '15 at 15:54
  • I was thinking of something like `SELECT * FROM data WHERE amount <= '1234%' AND amount >= '12_4'` with % and _ behaving like they do when using the like-keyword – fk2 Jun 12 '15 at 07:32
  • @fk2 What numbers would you expect to match your query? – Werner Henze Jun 12 '15 at 07:35
  • 1224 for example would be equal to 12_4 but smaller than any variation of 1234%, therefore data with an amount of 1224 should return. 1244 would return nothing because of the first clause. So any numbers that are smaller than 1234% and it's permutations and are bigger than any permutation of 12_4. – fk2 Jun 12 '15 at 07:41
  • 1
    @fk2 And what would be the practical use case of this? – Werner Henze Jun 12 '15 at 07:45
  • 1
    There is absolutely no one and I have been lobbying against it, but I'm not in charge of specs (yet) – fk2 Jun 12 '15 at 08:05