Case insensitive fields

[benny7]

Is is it possible to create a "chr" field in a table which is case insensitive when performing a select statement, e.g:

select descr from temp where desc like "%jam%"

Would return all instances of jam regardless of case e.g:

jAm
Jam
JAM

etc.....

I am not looking to change the select statement, perhaps there is a property with the field itself as there is with other databases.

Thanks

 

[Mike Gagnon]

benny7

The following seems to work for me.

CREATE CURSOR myCursor (name c(10))
INSERT INTO mycursor (name) VALUES ("Jam")
INSERT INTO mycursor (name) VALUES ("jam")
INSERT INTO mycursor (name) VALUES ("JaM")
SELECT name FROM ALIAS() WHERE UPPER('jam') $ UPPER(name)

Mike Gagnon

If you want to get the best response to a question, please check out FAQ184-2483 first.

 

[jimstarr]

No, I don't think you can do this without changing your SELECT statement. -Jim

 

[alvechurchdata]

If you are always going to look for lower case targets in your select statement then you could add another column which holds the lower-case equivalent of the target column. Something like:

Select desc, lower(desc) as target from temp into csrSearch

But you'd then have to change your select statement to search the new column so it still doesn't solve your original problem.

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[Mike Gagnon]

But you'd then have to change your select statement to search the new column so it still doesn't solve your original problem.

Neither is the suggestion you make, as it throws a "Syntax Error"...missing "into [red]cursor[/red] csrSearch"


Mike Gagnon

If you want to get the best response to a question, please check out FAQ184-2483 first.

 

[mspratt]

Hi benny7,

Borrowing from Mike Gagnon (with a twist):

CREATE CURSOR myCursor (name c(10))
INSERT INTO mycursor (name) VALUES ("Jam")
INSERT INTO mycursor (name) VALUES ("jam")
INSERT INTO mycursor (name) VALUES ("JaM")
SELECT name FROM ALIAS() WHERE ATC("jam",name)>0

Regards,

Mike

 

[Olaf Doschke]

Setting collate to general helps with =, but unfortunately not with like:
SET COLLATE TO "GENERAL"
CREATE CURSOR myCursor (name c(10))
INSERT INTO mycursor (name) VALUES ("Jam")
INSERT INTO mycursor (name) VALUES ("jam")
INSERT INTO mycursor (name) VALUES ("JaM")
* Will show all records:
SELECT name FROM mycursor WHERE name = "jam"
* Will show only "jam":
SELECT name FROM mycursor WHERE name like "%jam%"

That's not what you need, but I think you don't get around another select statement.

Bye, Olaf.

 

[ramani]

Hi

Without a change in the select command it is not possible to do it. However the select command need to be fixed to take care of both right and left side of the comparison.

I just believe, you statement is an example only.

You should take care that 'jam' in the above is either UPPER or LOWER case suitably just as you do in the statement..
select descr from temp where UPPER(desc) like "%jam%"
or
select descr from temp where LOWER(desc) like "%jam%"



____________________________________________
ramani - (Subramanian.G)

http://winnersoft.coolfreepages.com/