-4

I have the following query

String sql = select id from sheet_tab where filename in ('value.xls','export.xls')

and I am executing it using 

Statement st = con.createStatement();
ResultSet rs = st.executeQuery(sql);

Now the problem is there is another file in my db with name of 'Export.xls' and when I try to fetch for 'export.xls' it doesn't return me any id (which I am finding it strange), but seems to work fine when 'Export.xls' is passed and it fetches the concurrent id for that particular file.

Can anyone pls help me with this.???

I want a query which wd fetch the result set for 'export.xls' and NOT 'Export.xls' (i.e. as case sensitive one)

P.S. LOWER func wont work as the filename is getting fetched by a javacode dynamically so we don't actually know what are the filenames that are being queried, e.g. it can be value.xls, VaLue.xls , ExPort.xls.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Dipayan Dey
  • 13
  • 6
  • 1
    Your question is not quite clear. Does the db contain both `export.xls` and `Export.xls`? – Jim Garrison May 27 '15 at 07:27
  • So your question is really: How to do case insensitive text comparisons in SQL? – Adriaan Koster May 27 '15 at 07:30
  • 1
    And you are not getting a result for `export.xls` but ARE getting a result for `Export.xls`? If that is the case then you must be mistaken that one entry exactly matches `export.xls` in the db. You need to show the expected results and the actual content of the database. – Jim Garrison May 27 '15 at 07:30
  • 1
    You can tell your question is unclear because others are misinterpreting it to mean you want case-insensitive query, which does not follow from what you've written so far. – Jim Garrison May 27 '15 at 07:32
  • 1
    Your problem is not that the SELECT statement isn't case-sensitive. Your problem is that nothing in the database matches 'export.xls', contrary to your expectation. Or else this isn't the real query. – user207421 May 27 '15 at 07:39
  • Learn about prepared statements. – Jens May 27 '15 at 07:43
  • 2
    __How to make SELECT statement case sensitive in jdbc__ It already is! You are not getting Export.xls when you search for export.xls. Ans export.xls probably does not exist. So please be clear what it is that you are struggling with. – CodeNewbie May 27 '15 at 07:44
  • @JimGarrison pls find the editted question – Dipayan Dey May 27 '15 at 07:45
  • the entry very much exists. the query works fine when i run it on dbeditor directly but doesnt fetch anything when i query via jdbc I kno this sounds strange, even I m puzzeled – Dipayan Dey May 27 '15 at 07:46
  • 1
    Run this: `select id from sheet_tab where lower(filename) like 'export%'` and add the results to your question. – Jim Garrison May 27 '15 at 07:49
  • And show us the real code. This isn't it. You've said yourself that the filenames come from variables. – user207421 May 27 '15 at 08:18

1 Answers1

0
String sql = select id from sheet_tab where filename in ('value.xls','export.xls')

String sql = select id from sheet_tab where ucase(filename) in ('VALUE.XLS','EXPORT.XLS')

    String sql = select id from sheet_tab 
where UCASE(filename) in (UCASE('value.xls'),UCASE('export.xls'))
danny117
  • 5,581
  • 1
  • 26
  • 35