firebird-devel

[Firebird-devel] SUBSTRING Function

[Andrea C. P. <ac...@bi...>]

Hi all,

with Firebird 1 comes new function SUBSTRING.

This function returns right value but create a field in the return cursor of
a wrong lenght.

An example:

table test with field ftest char(10)
table has 1 record '0123456789'

if I run sql code

select substring(ftest from 1 for 2) as subftest1,substring(ftest from 3 for
2) as subftest2,substring(ftest from 1 for 2) || substring(ftest from 3 for
2) as subftest3  from test

it returns

subftest1    subftest2   subftest3
----------  ----------  ----------
01             23             0123

and it is right but fields subftest1 and subftest2 are 10 char lenght so
when in my application I do subftest1+subftest2
I have

01          23
and not 0123

Probably results fields are initialized as varchar(field lenght) and then
filled with a null terminated string.
It is not possible inizialize field as CHAR(len of FOR clause) in new
firebird versions since we know the lenght we want return string?

All database I worked with (DB2, ORACLE, SQL) returns exact lenght so I have
a lot of applications to convert to firebird but a lot of line of code to
adjust

Thanks
--
Andrea Chiadò Piat
Bit Informatica s.r.l.
Progettazione e Sviluppo

RE: [Firebird-devel] SUBSTRING Function

[Claudio V. C. <cv...@us...>]

> -----Original Message-----
> From: fir...@li...
> [mailto:fir...@li...]On Behalf Of Andrea
> Chiado' Piat
> Sent: Lunes 29 de Julio de 2002 13:56
>
> and it is right but fields subftest1 and subftest2 are 10 char lenght so
> when in my application I do subftest1+subftest2

Do you mean subftest1 || subftest2, right? We don't support the "+", the
ANSI operator is "||".


> I have
>
> 01          23
> and not 0123
>
> Probably results fields are initialized as varchar(field lenght) and then
> filled with a null terminated string.

No. But this is problem that comes from the time the function was done
originally. It was an ASCII thing, not able to tackle international charsets
at all. It was used for internal tasks only. But in general terms, substring
seems to work okay. The problem is concatenation: it currently doesn't
follow the ANSI-SQL specification that has detailed rules to mix char and
varchar.


> It is not possible inizialize field as CHAR(len of FOR clause) in new
> firebird versions since we know the lenght we want return string?

I don't think your suggestion applies at all. We only know there's a
divergence WRT the standard. Either substring and/or concatenation should be
polished.


> All database I worked with (DB2, ORACLE, SQL) returns exact
> lenght so I have
> a lot of applications to convert to firebird but a lot of line of code to
> adjust

If I remember well, char will always produce that effect, so the correct
case to fix is when varchars are involved.

C.

Re: [Firebird-devel] SUBSTRING Function

[Andrea C. P. <ac...@bi...>]

Hi,

Claudio,

> Do you mean subftest1 || subftest2, right? We don't support the "+", the
> ANSI operator is "||".

sorry, you are right. I use "+" only in my frontend application
(VisulFoxPro). In SQL I use "||"

Dimitry,

>I don't think that the type of SUBSTRING() result may be changed in
>the future versions of FB. At first, it would make problems with
>compatibility. Second, some other people (including me) would like
>the result to be an VARCHAR because they don't need trailing blanks.

Ok. I dont think VARCHAR is the real problem. Varchar is also ok for me but

if field is char(30) or varchar(30)

and I write substring as SUBSTRING(field FROM 1 FOR 5)

it returns VARCHAR(30)

and not VARCHAR(5) since we know len of return string we want with FOR
clause?

Thank you

--
Andrea Chiadò Piat
Bit Informatica s.r.l.
Progettazione e Sviluppo

""Claudio Valderrama C."" <cv...@us...> ha scritto nel messaggio
news:NBB...@us......
> > -----Original Message-----
> > From: fir...@li...
> > [mailto:fir...@li...]On Behalf Of Andrea
> > Chiado' Piat
> > Sent: Lunes 29 de Julio de 2002 13:56
> >
> > and it is right but fields subftest1 and subftest2 are 10 char lenght so
> > when in my application I do subftest1+subftest2
>
> Do you mean subftest1 || subftest2, right? We don't support the "+", the
> ANSI operator is "||".
>
>
> > I have
> >
> > 01          23
> > and not 0123
> >
> > Probably results fields are initialized as varchar(field lenght) and
then
> > filled with a null terminated string.
>
> No. But this is problem that comes from the time the function was done
> originally. It was an ASCII thing, not able to tackle international
charsets
> at all. It was used for internal tasks only. But in general terms,
substring
> seems to work okay. The problem is concatenation: it currently doesn't
> follow the ANSI-SQL specification that has detailed rules to mix char and
> varchar.
>
>
> > It is not possible inizialize field as CHAR(len of FOR clause) in new
> > firebird versions since we know the lenght we want return string?
>
> I don't think your suggestion applies at all. We only know there's a
> divergence WRT the standard. Either substring and/or concatenation should
be
> polished.
>
>
> > All database I worked with (DB2, ORACLE, SQL) returns exact
> > lenght so I have
> > a lot of applications to convert to firebird but a lot of line of code
to
> > adjust
>
> If I remember well, char will always produce that effect, so the correct
> case to fix is when varchars are involved.
>
> C.
>
>
>
> -------------------------------------------------------
> This sf.net email is sponsored by: Dice - The leading online job board
> for high-tech professionals. Search and apply for tech jobs today!
> http://seeker.dice.com/seeker.epl?rel_code=31
> _______________________________________________
> Firebird-devel mailing list
> Fir...@li...
> https://lists.sourceforge.net/lists/listinfo/firebird-devel
>