At 02:00 PM 20/02/2004 -0600, you wrote:

>Hi,
>We have been using a front end for all our production management, and we
>have since decided to change the format of our part numbers. The software we
>have runs off of Firebird, and since the software offers no utilities to do
>what I need (at least with out manually changing them all) I belive the
>easiest way to make the changes is just to do them with IBConsole.
>Currently our parts are in the form of two letters, followed by the actual
>part number ("AA######...." ). What I need to do is parse out the first two
>letters of the part number field and update it, leaving only the numbers. As
>I see it from reading the Language Reference, I would need to use the substr
>funtion to do that acutal parsing and the update command to store it back
>in. I have a some what limited knowledge of SQL, having only experienced
>MySQL in small, limited, web site implemetations. Knowing that, this is how
>I decided to go about it:
>
> update PARTSTABLE set PARTNUM=substr(PARTNUM,2,strlen(PARTNUM)-1) where
>PARTNUM like 'AA%';
>
>When I ran that in IBConsole, I got a "variable undefined" error for the
>reference to "PARTNUM" that was the first argument in "substr." When I
>looked at the docs for the update command I saw this:
> Notes on the UPDATE statement
>
> In SQL and isql, you cannot use val as a parameter placeholder (like
>"?").
>
> In DSQL and isql, val cannot be a variable.
>
>Since I can't use variables inside this type of statement, I am at a loss as
>to how I should go about this. Any help will be appreciated.

Use the internal SUBSTRING function. Now, let's assume PARTNUM is a
varchar(20) - change your numbers to suit.

update PARTSTABLE
set PARTNUM = SUBSTRING(PARTNUM FROM 3 FOR 18)
where PARTNUM STARTING WITH 'AA';

Note: In addition to the IB 6 LangRef, you also need the release notes!!

/heLen