Menu
I am using java.sql.DatabaseMetaData#getTables per this JavaDoc.
What should I use for the tableNamePattern if I want to look up a table and ignore case? Where is this pattern format documented?
Can I craft a pattern here that will match "loinc", "LOINC", or "Loinc"?
902
Case insensitive SQL SELECT: Use upper or lower functions or this: select * from users where lower(first_name) = 'fred'; As you can see, the pattern is to make the field you're searching into uppercase or lowercase, and then make your search string also be uppercase or lowercase to match the SQL function you've used.
Introduction to SQL Case Insensitive SQL Case insensitivity is to use the query statements and the keywords tables and columns by specifying them in capital or small letters of alphabets. SQL keywords are by default set to case insensitive that means that the keywords are allowed to be used in lower or upper case.
LIKE performs case-insensitive substring matches if the collation for the expression and pattern is case-insensitive. For case-sensitive matches, declare either argument to use a binary collation using COLLATE , or coerce either of them to a BINARY string using CAST .
How to get the object of DatabaseMetaData: The getMetaData() method of Connection interface returns the object of DatabaseMetaData. Syntax: public DatabaseMetaData getMetaData()throws SQLException.
Can I craft a pattern here that will match "loinc", "LOINC", or "Loinc"?
In general: No. The JDBC API does not offer a way to perform a case-insensitive query for a specific table.
In practice this is often not a problem, as most DBMS' use only upper case for table names. The ANSI SQL standard requires table names in SQL queries be converted to upper case, unless they are quoted (using "). Since constantly quoting is rather tedious, most developers and most software use table names in upper case. So in most situations you can just assume the table name is in upper case, and use a pattern in upper case.
If you really need a case-insensitive query, you can specify a tableNamePattern of null (which means not to filter by name), then iterate through all the results and check the table name in Java code. Just be aware that this may be slow for databases with very many tables.
61
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
