Select statement issue

I just found something interesting, but a problem none the less. Here's the select statement:

Select * From "MstrNames" Where "COLastName" > 'b'

I was performing this select in my app but not returning any records. So, I tried the same SQL statement in the db (I am using IBExpert's sql editor) and still no records in the results. So, on a whim, I tried this select statement:

Select * From "MstrNames" Where "COLastName" > 'B'

And wouldn't you know it, it found the five records I was looking for. The interesting thing is if I fill the dataset on the form load with all the records (Select * from "MstrNames") and then perform a rowfilter on a dataview of the ds with:

dv.Table = ds.Tables("""MstrNames""")
dv.rowfilter = "COLastName > 'b'"

It will return the five records in the dv. The problem is, when I fill the dataset on the form load I may be loading upwards of 5000 records when the user may only be looking for three or five records, so I just perform the SQL on the dataset fill each time they perform a search.

My problem is, how can I get the dataset to fill with the desired records without having to have the user put in the name in Proper format ie. James, John instead of james, john.

I can't be sure that the user will always input the information in proper format when inserting a new record. Thank you for the help.

So, no one else has run into this problem??

Why is it that you put the table and column names in double quotes. This is not necessary.

Regarding your question:

You could try

Select * From MstrNames Where upper(COLastName) > Upper('B')

This way the comparison works on upper case values only, "ignoring" the case the user entered.

This will probably prohibit any index to be used though

Thanks. I didn't realize there was an upper function able to run in a sql statement.

SELECT * FROM USERS WHERE USER_NAME =: test1
i did try the upper no success...

any ideas!??


thank you!

Try using:
"WHERE XXXX STARTING WITH UPPER(:PARAMETER)"

SELECT * FROM USERS WHERE UPPER(USER_NAME) = 'TEST1' and UPPER(USER_PASSWORD) = 'PASSWORDTEST';

it works this way thanks...
i'm posting it cuz it may help some other ppl..

Thank you!