From: Zabach, Elke (elke.zabachsap.com)
Date: Wed Jul 06 2005 - 09:28:24 CDT

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]

Uwe Haussmann wrote:
>
> Hello,
>
> I come back to my question about SUBSTR which I posted some time ago but
> which has never been answered.

Sorry, was somewhere lost in noone's land.

>
> I want to retrieve rows which fulfill a certain search criteria
> contained in a LONG column, what, after upgrading to v.7.6.10 should be
> possible. I work under windows XP with .NET 1.1 using the latest ODBC
> driver(Unicode) as the database is a unicode database.
>
> I have only pure text data in that column, the longest content is about
> 60.000 characters.
>
> My query is :
>
> SELECT OID, LANGUAGEID, CONTENT FROM TABLENAME
> WHERE LOWER(SUBSTR(CONTENT,1,2000)) LIKE '%sehr geehrter%'
> AND LANGUAGEID ='de'
>
> this works. The result is found in a row containing only 224 characters.
>
> If I say 3000 instead of 2000 I get the error missing value specification.
>

This I cannot reproduce.
This error occurs, if no third value is given.
The third value is needed if the first one is a LONG-column, thus defining the maximum length of that column, which may not exceed the normal column length of 8000 bytes (CHAR (8000) ASCII / CHAR (4000) UNICODE).
We will describe in more detail.
 
> I would like to specify the WHERE condition like follows:
>
> WHERE LOWER(SUBSTR(CONTENT,1)) LIKE '%sehr geehrter%'
>
> as I want the complete column to be searched, but I get the same error.

Sorry, this will never be possible as we need the maximum length to provide enough space in pages.
--> You will only be able to check portions of longs (perhaps in a loop over the long-column, the loop being in a dbproc/dbfunction).

Elke
SAP Labs Berlin

> The help file says the SUBSTR(x,a) should work.....
>
> I tried what I think instinctively everyone would have done first:
>
> WHERE LOWER(SUBSTR(CONTENT,1, LENGTH(CONTENT))) LIKE '%sehr
> geehrter%''
>
> but here also the same error.
>
> How can I get my results ? Help would be very much appreciated.
>
>
>
> --
> mit freundlichen Grüßen
> Uwe Haussmann
> mailto: uhaussmannsomdet.net
> PGP/GNUPG Public Key: http://www.somdet.net/pgp/uhaussmann.txt
>
>
> --
> MaxDB Discussion Mailing List
> For list archives: http://lists.mysql.com/maxdb
> To unsubscribe: http://lists.mysql.com/maxdb?unsub=elke.zabachsap.com

--
MaxDB Discussion Mailing List
For list archives: http://lists.mysql.com/maxdb

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]