At 06:35 PM 30/08/2003 +0000, you wrote:

>Hello everyone
>
>How do I return a partial field value in sql in InterBase?
>
>ie When I use the mid function in an Access query like this:
>
>SELECT Mid([SomeField],1,5) AS Expr1
>FROM SomeTable;
>
>if returns the first 5 characters, starting from the beginning of
>the value, how do I do the same with InterBase?

InterBase doesn't have the SUBSTRING function, so you will need to use the
UDF (i.e. external function) substr(..).
1. Make sure ib_udf.dll is in the ../udf subdirectory and ib_utils.dll is
in the ../lib directory
2. Pick up the sql for declaring the substr udf from ib_udf.sql, which
should be in the ../examples directory
3. In your admin client or isql, paste the declaration into a DDL
interface window and commit it (though people usually install UDFs via
scripts).

Now you are ready to use the UDF.

Syntax will be
SELECT substr(SomeField, 1, 5) AS Expr1
FROM SomeTable;

Notice that, in the IB version of this UDF, the semantics of the third
argument is different to what you would expect. It is not the length of
the substring but the position of the last character.
So, if you wanted to get 5 characters starting at pos 2, you would need to
specify
SELECT substr(SomeField, 2, 6) AS Expr1
FROM SomeTable;

Borland's version will also return null if the third argument is past the
end of the input string.
heLen