Subject RE: [IBO] Trouble creating a query using SUBSTR
Author Eric Tishler
Thank you for all the tips. I have the IBO help files, which are not the most useful, but I do have the IB LangRef.pdf manuals which are slightly better. Still, I have found the actual help section on the IBObjects web site the best for general explanations and 'how to' examples. I will be doing a lot of work with IBObjects over the next few months.

I actually have solved this problem before I left last night. Of course now that I have the query working, I will need to integrate this into a QuickReport today.

Yours tips on performance and efficiency are most useful.



Eric Tishler
Software Architect
Resolute Partners, LLC
Phone: 203.271.1122
Fax: 203.271.1460

-----Original Message-----
From: Helen Borrie [mailto:helebor@...]
Sent: Thursday, March 18, 2004 6:02 PM
Subject: Re: [IBO] Trouble creating a query using SUBSTR

At 06:02 PM 18/03/2004 +0000, you wrote:
>I am using TIBOQuery and I need to use a substring of on of the
>fields in my query to make the WHERE clause work. However, when I try
>to run my application (in Delphi 6) I throw an exception, SQL Error -
>804, Function unknown SUBSTR.
>My where clause appears as below ...
> "Transactions"."TransactionTypeID" = '7110' AND
> (substr("Transactions"."CrossReference",1,1) = 'A' OR
> substr("Transactions"."CrossReference",1,1) = 'V')

You are getting the error because the UDF substr() isn't declared in the
database. Firebird has an internal function SUBSTRING. But a function is
not as efficient as
"Transactions"."TransactionTypeID" = '7110' AND
"Transactions"."CrossReference" STARTING WITH 'A' OR
"Transactions"."CrossReference" STARTING WITH 'V'


"Transactions"."TransactionTypeID" = '7110' AND
SUBSTRING("Transactions"."CrossReference" FROM 1 FOR 1) IN ('A' , ''V')

If you are using IB, you will need to declare and use the UDF.

>I know I can accomplish this with a where clause like this ...
> "Transactions"."TransactionTypeID" = '7110' AND
> ("Transactions"."CrossReference" LIKE 'A%' OR
> "Transactions"."CrossReference" LIKE 'V%')
>but later on I will need to look at substrings that are not just the
>first character in the field (allowing me to use LIKE)

Like is for wildcard searches. If you are looking for an enclosed string,

"Transactions"."TransactionTypeID" = '7110' AND
"Transactions"."CrossReference" CONTAINING 'XYZ' OR
"Transactions"."CrossReference" CONTAINING 'PQR'

CONTAINING is case-insensitive.


Do you have the IB manuals, esp. LangRef.pdf?


IB Objects - direct, complete, custom connectivity to Firebird or InterBase
without the need for BDE, ODBC or any other layer.
___________________________________________________________________________ - your IBO community resource for Tech Info papers,
keyword-searchable FAQ, community code contributions and more !


Yahoo! Groups Links
* To visit your group on the web, go to:

* To unsubscribe from this group, send an email to: <>

* Your use of Yahoo! Groups is subject to the Yahoo! Terms of <> Service.

[Non-text portions of this message have been removed]