At 04:23 PM 07-01-02 +0900, you wrote:

>I know that like this...
>
>' select substring(account from 1 for 3 ) from vuser where account ='shyang'; '
>
>I want to user substring in order by clause like this..
>
>'select account from vuser order by substring(fullname from 1 for 3) asc ; '
>
>But in interbase, this statement failed...

InterBase(R) doesn't support an internal function SUBSTRING - you need Firebird !

If you want a substring function using InterBase(R), you need to declare the user-defined function SUBSTR from the ib_udf.dll.

Here's the declaration:

/*****************************************
*
* s u b s t r
*
*****************************************
*
* Functional description:
* substr(s,m,n) returns the substring
* of s which starts at position m and
* ending at position n.
* Note: This function is NOT limited to
* receiving and returning only 80 characters,
* rather, it can use as long as 32767
* characters which is the limit on an
* INTERBASE character string.
* Change by Claudio Valderrama: when n>length(s),
* the result will be the original string instead
* of NULL as it was originally designed.
*
*****************************************/
DECLARE EXTERNAL FUNCTION substr
CSTRING(80), SMALLINT, SMALLINT
RETURNS CSTRING(80) FREE_IT
ENTRY_POINT 'IB_UDF_substr' MODULE_NAME 'ib_udf';

/*****************************************
Make sure you get the Firebird version of ib_udf.dll - the InterBase(R) version has bugs.

Documentation for ib_udf.dll is in Chapter 5 of the InterBase Language Reference manual.

regards,
Helen

All for Open and Open for All
Firebird Open SQL Database · http://firebirdsql.org
_______________________________________________________