SQL Help with long varchar field

I'm trying to write SQL that will allow me to perform a case insensitive search against a long varchar field. This is a vendor supplied database, so changing the database isn't an option. UPPER/UCase and LOWER/LCase do not work because it is a long varchar field. Does anyone have any suggestions?

What happens when you try the UCASE/LCASE functions against substrings you KNOW should be found? Is the data in the varchar field in mixed case? What about the search arg? Cannot say that I have done this or that I have not! Could you do:
where (SomeColumn =BLAH OR SomeColumn = blah or SomeColumn = Blah)?

fv

When I try UCASE/LCASE it fails. I get an error message that states "No function or procedure was found with the specified name (UCASE) and compatible arguments." VENDORERRORCODE -440, SQLSTATE 42884

UCASE/LCASE work fine on all other fields - just not LONG VARCHAR.

And yes - the data can be mixed case. I wouldn't want to search for every possible upper/lower combination. If the search was for "ONStor", I would have to perform 722 different comparisons (from "onstor" to "ONSTOR" and everything in between).

UCASE,UPPER,LCASE, and LOWER all wotk on Varchar on both Z/os and UDB for me. Could be a limitation on LONG VARCHAR?

Can you do:
UCASE(VARCHAR(mycolumn))
?

fv

It was a good thought..but it doesn't work. The reason the field is LONG VARCHAR is because of the length. Converting it to VARCHAR truncates part of the field and makes the "search" meaningless. Nice try though....

Hmm - max size of a varchar is 32,672.
Max size of a long varchar is 32,700. Not a lot of difference.
What on earth are you storing that has to be so large? he entire text of "War and Peace"? Seems like a rather unmanagable "chunk" of data....

fv

It's a vendor application - I'm just trying to write a mini-app to present the information in a user-friendly format. The field is used to store all comments made regarding a particular issue.

I would propose that such is a horrible,horrible design; but nothing you can do about their foolishness....

I there any way you can pull the content of this field out in manageble-sized "chunks"? Anything to delimit one comment from the next? If so, so might can build your own recordset or other such data structure and search that....just a wild thought.

fv

In a worse case scenario you could create a summary table or a view of your current table splitting the contents of the long varchar field into two seperate fields so that they could be cast into varchar without damaging them.
Then do your searches against the view or summary table.