Hi,
I'm moving over from MS SQL. I've been googling this for ages now and
suprisingly cannot find a clear answer.
I want my data in tables to be case insensitive.
This is so i can:
1. Put keys on natural key fields, for instance a product part number. I
product number 'ABC123' inserted i need to disallow 'abc123' to be inserted as
a second row. Please don't tell me i have to add another column holding a
lowered version of the product number. My database is littered with this need
and i would end up bloating my table schema.
2. I need to query case insensitively. SELECT * FROM product WHERE
product_number = 'ABC123' should return the same row as SELECT * FROM product
WHERE product_number = 'abc123'
Is there a database wide collation setting i can make, or a case insensitive
character type. There are lots of online posts regarding using LOWER function
for querying. This is a workaround for point (2) but does not remedy point (1)
above.
Many thanks
Phillip
Phillip Smith
