Home > other >  SQL WHERE column values into capital letters
SQL WHERE column values into capital letters

Time:11-06

Let's say I have the following entries in my database:

Id Name
12 John Doe
13 Mary anne
13 little joe
14 John doe

In my program I have a string variable that is always capitalized, for example:

myCapString = "JOHN DOE"

Is there a way to retrieve the rows in the table by using a WHERE on the name column with the values capitalized and then matching myCapString?

In this case the query would return two entries, one with id=12, and one with id=14

A solution is NOT to change the actual values in the table.

CodePudding user response:

A general solution in Postgres would be to capitalize the Name column and then do a comparison against an all-caps string literal, e.g.

SELECT *
FROM yourTable
WHERE UPPER(Name) = 'JOHN DOE';

If you need to implement this is Knex, you will need to figure out how to uppercase a column. This might require using a raw query.

  • Related