0

I have a database search application which can actually find registries between two values ,the filters are like the ones in this example:

String sql ="SELECT * FROM   Registries\n"+
            "AND Registries.Registry\n"+
            "BETWEEN '"+concatCode1+"'\n"+
            "AND '"+concatCode2+"'";

Where concatCode1 is for example : 15C9999

Where 15 is the year, C is the type of registry and 9999 is the number of that registry.

So it works pretty good, except if the concatCode1 concatCode2 are shorter like 15C100 and 15C250. the program will list from:

15C10000 instead of 15C00100, to 15C25000 instead of 15C00250.

Also there is another paramather which distincts if between the year and the number there is a letter (which can be -> C,B,M,D,A), or a "_" (without quotes) which in MySQL is used to include all characters, for example, having this solo value: 

String sql ="SELECT * FROM   Registries\n"+
            "AND Registries.Registry\n"+
            "LIKE 15_9999";

should be listing 15A9999, 15B9999, 15C9999, 15D9999, 15M9999.

So, what I need is beeing able to list every kind of registry using the Between statement with something that emulates the functionality of the statemen LIKE, because i can't use the "_" in my query using BETWEEN.

EDIT: I'm trying this like that: 

"BETWEEN '"+codeYear1+codeTestType+"LPAD('"+code1+"' ,5 ,'0')'\n"+                                
"AND '"+codeYear2+codeTestType+"LPAD('"+code2+"' ,5 ,'0')'";

Throws MysqlError on '"+code2+"' ,5 ,'0')

Jesús Suárez Borrell
  • 7
  • 4
  • You may escape the underscore joker with the backward slash : '\\_' – Arnaud Dec 10 '15 at 16:51
  • To resolve this you need to break out the different components of your code. you're trying to treat character data like numeric data sometimes, and like character data others. so take the left 3 characters and then [lpad](http://stackoverflow.com/questions/11165104/adding-a-leading-zero-to-some) the remaining characters with 0's. for what 5 positions? – xQbert Dec 10 '15 at 16:53
  • I'm trying this like that: BETWEEN '"+codeYear1+codeTestType+"LPAD('"+code1+"' ,5 ,'0')' AND '"+codeYear2+codeTestType+"LPAD('"+code2+"' ,5 ,'0')'; Throws MysqlError on '"+code2+"' ,5 ,'0') – Jesús Suárez Borrell Dec 10 '15 at 18:15

0 Answers0