Neohapsis is currently accepting applications for employment. For more information, please visit our website www.neohapsis.com or email firstname.lastname@example.org
AW: Substr on Long columns
From: Zabach, Elke (elke.zabachsap.com)
Date: Wed Jul 06 2005 - 09:28:24 CDT
Uwe Haussmann wrote:
> 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).
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
> 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