Subject Re: [firebird-support] FB/IB equivalent of LEFT(string, count)
Author Helen Borrie
At 12:37 PM 13/12/2003 +0000, you wrote:
>Thanks for the reply, Helen - see below:
>----------Original Message---------
> > At 11:18 AM 13/12/2003 +0000, you wrote:
> > >I'm new to FB, and my old SQL knowledge seems to have deserted me here.
> > >
> > >Using 'LEFT(field_name, count)' in a WHERE clause of a query gets me a
> > >'TOKEN unknown' error.
> > >
> > >I thought this was standard SQL, but evidently FireBird has other
> > ideas.
> >
> > Standard SQL for what? The LEFT keyword in SQL is reserved. It's used
>I realise it's a reserved word, that much I knew. It is where/how it can
>be used that seems to be in conflict with other SQL usage. However, in
>SQLServer, Access, FoxPro (prior to MS), the use of:
>SELECT field1, field2 FROM Table
>WHERE LEFT(field3,3) = 'foo'
>has always worked in the past - but apparently not in FB1.5.

ROFL, you picked the three *least* standards-compliant DBMS's. :-}

>so has:
>SELECT LEFT(field1,3) AS foobar
>FROM Table

Pure standard:

SELECT SUBSTRING(field1 FROM 1 for 3) AS foobar
FROM Table

>I'm in the process of converting a ColdFusion web app from using Access
>mdbs to FB1.5, hence the problem.

How about I tell you NOW (saving your next problem) that "+" and "&" are
not legal string concatenators in standard SQL. Use double-pipe "||"
(that's two pipe characters, no space between).