0

Right now I'm writing a java program that is an API to run queries on an SQL database. There is a search API call which compares the parameters to several fields and it changes the parameters to lower case and the data to lowercase when comparing so there is no issues. For example

SELECT
    ...
FROM
    ...
WHERE
    LOWER( position.positionname ) = parameters;

This part is working, however, there is one data type that I am having trouble comparing because it is an ENUM datatype called location. Here is the SQL. 

CREATE TYPE location AS ENUM
(
    'New York', 'Los Angeles', 'Chicago',
    'London','Paris','Tokyo'
);

I can't use the lower() function on it because it is not a string. Someone searching for New York on our application may not necessarily capitalize the correct letters but we still want the application to work. Is there a way to convert enum to string and then lower() it all within the SQL query?

JT Holden
  • 1
  • 1
  • 2
  • 2
    Very few DBMS support a native `ENUM` in SQL, but you haven't told us which one you're using, so which DBMS is this? What does the code that generates the SQL look like? – Dai Nov 29 '18 at 05:01
  • Also, `WHERE LOWER( position.positionname ) = parameters` is bad SQL for two reasons: if the data is text then it's unnecessary because text comparisons are case-insensitive by default in pretty much every SQL implementation, and wrapping a column in a function call means the query is no longer "sargable" (see https://stackoverflow.com/questions/799584/what-makes-a-sql-statement-sargable ) which means the DBMS cannot use indexes efficiently and may need to perform a full table-scan for every query (this is bad). – Dai Nov 29 '18 at 05:03
  • @Dai Im not sure exactly what DBMS means. I'm using PGADMIN4 hosted on Postgres. – JT Holden Nov 29 '18 at 05:06
  • A DBMS is the name of the database product like MS SQL Server, MySQL, MariaDB, Postgres, IBM Db2 and Oracle DB - you've mentioned you're running on Postgres which does support `ENUM`, so I recommend you read this article: https://www.postgresql.org/docs/9.1/datatype-enum.html - but please post actual code from your project that demonstrates the problem you're having. – Dai Nov 29 '18 at 05:08
  • Why can't you just redefine your Postgres enum to be case insensitive, e.g. all lowercase? – Tim Biegeleisen Nov 29 '18 at 05:12
  • @TimBiegeleisen unfortunately I did not create the database or write any of the SQL for it. I am creating an API that runs search queries and such on it. I can ask the other guy to change it however and I might have to. – JT Holden Nov 29 '18 at 05:18
  • A possible temporary workaround: Create a temp table which maps the enum locations to a lowercase version. Then, join to this temp table, and compare using the case insensitive (e.g. lowercase) versions. Either this, or you use case insensitive regex comparisons. – Tim Biegeleisen Nov 29 '18 at 05:20

2 Answers2

2

It may sound strange but you need to cast the enum type to string, or text type in order to run a string function on it using either cast(position.positionname as text) or shorthand position.positionname::text

SELECT
    ...
FROM
    ...
WHERE
    LOWER( position.positionname::text ) = parameters;
Lucas
  • 600
  • 3
  • 9
0

As far as I was able to determine and have just tested it, a query on an ENUM column is case INsensitive. So in the original question, a search for 'new york' or 'NEW YORK' or New YORK' or any other case variation would all find and return the entry as in the original ENUM column declaration, i.e. 'New York'.

So case variations in user input can be ignored when dealing with ENUMs (and SET type too I believe, but not yet tested).

UKenGB
  • 21
  • 2