By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,589 Members | 2,276 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,589 IT Pros & Developers. It's quick & easy.

DB2 Clob Serach case insensitive

P: n/a
Hello DB2 Experts,
I have a Clob column and I need to search for a String in that.
Case insensitive search.

I tried,
select * from MyTable where UPPER(varchar(CLOB_COLUMN)) LIKE
'%some-data%'

[IBM][CLI Driver][DB2] SQL0171N The data type, length or value of
argument "1" of routine "UPPER" is incorrect. SQLSTATE=42815

DB2 version: DB2 on OS/390 07.01.0001
Appreciate any help.
Thanks,
Srini.
Nov 12 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
sr******@yahoo.com (Srini) wrote in message news:<23**************************@posting.google. com>...
Hello DB2 Experts,
I have a Clob column and I need to search for a String in that.
Case insensitive search.

I tried,
select * from MyTable where UPPER(varchar(CLOB_COLUMN)) LIKE
'%some-data%'

[IBM][CLI Driver][DB2] SQL0171N The data type, length or value of
argument "1" of routine "UPPER" is incorrect. SQLSTATE=42815

DB2 version: DB2 on OS/390 07.01.0001
Appreciate any help.
Thanks,
Srini.

In "DB2 Universal Database for OS/390 and z/OS SQL Reference Version
7", for UCASE or UPPER function, it says that "string-expression .....
A character string argument ..... and must have an actual length that
is not greater than 255."

So, it seems that you can not change a string longer than 255 to upper
case with one UPPER or one TRANSLATE function.
Nov 12 '05 #2

P: n/a
Tokunaga, Thank you for your quick response.
Are there any other options OR is it Impossible to do case insensitive
search on DB2 Clob columns [DB2 on os/390 ver 7.1]

Thanks,
Srini.
Nov 12 '05 #3

P: n/a
Srini wrote:
Tokunaga, Thank you for your quick response.
Are there any other options OR is it Impossible to do case insensitive
search on DB2 Clob columns [DB2 on os/390 ver 7.1]


You could write your own UDF.

--
Knut Stolze
Information Integration
IBM Germany / University of Jena
Nov 12 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.