SQL Use Greater/Less Than with like and wildcards

Question

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!

Answer 1

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.