Entered criteria | Description | Resulting SQL |
John Doe | Field contains exact string | somefield = 'John Doe' |
*John* | field contains entered string | somefield like '%John%' |
120 | Field containing exact number | somefield = 120 |
120..160 | Interval between 120 and 160 | somefield between 120 and 160 |
<>100 | Field not equal to entered value (less than, greater that also should work) | somefield <> 100 |
<100&>20 | And operater | somefield < 100 and somefield > 20 |
%John%|%Paul%|%Mary% | Or operater | (somefield like '%John%' or somefield like '%Paul%' or somefield like '%Mary%') |
100|(>30&<50) | Use of parentheses | ((somefield = 100) or (somefield > 30 and somefield < 50)) |
'a*' | Single quote filters exact expression so special characters could also be searched | somefield = 'a*' |
'' | Two single quotes enable search of empty expressions | somefield = '' |
12|1103|210321 | Date fields expansion: if one or two digits is entered it refers to current month, if four digits are entered it refers to current year | (somedate = '2022-04-12' or somedate = '2022-03-11' or somedate = '2021-03-21') |
Some rules i came up with for myself, since i had to do something along those lines:
Entered criteria Description Resulting SQL John Doe Field contains exact string somefield = 'John Doe' *John* field contains entered string somefield like '%John%' 120 Field containing exact number somefield = 120 120..160 Interval between 120 and 160 somefield between 120 and 160 <>100 Field not equal to entered value (less than, greater that also should work) somefield <> 100 <100&>20 And operater somefield < 100 and somefield > 20 %John%|%Paul%|%Mary% Or operater (somefield like '%John%' or somefield like '%Paul%' or somefield like '%Mary%') 100|(>30&<50) Use of parentheses ((somefield = 100) or (somefield > 30 and somefield < 50)) 'a*' Single quote filters exact expression so special characters could also be searched somefield = 'a*' '' Two single quotes enable search of empty expressions somefield = '' 12|1103|210321 Date fields expansion: if one or two digits is entered it refers to current month, if four digits are entered it refers to current year (somedate = '2022-04-12' or somedate = '2022-03-11' or somedate = '2021-03-21')
So, what would be the way to parse this expressions so I can build SQL expressions the way I want to?