Postgres Pro
Downloads
Home   >   mailing lists

Thread: substring problem

substring problem

From
Sh A Guru Prasad
Date:
select trn_id,substr(hoa_id,1,13) from  tbill_master where
substr(hoa
_id,1,13)='2202031040006';
     trn_id     |    substr
----------------+---------------
 25001203000416 | 2202031040006
 25001203000417 | 2202031040006
 25001203000418 | 2202031040006
 25001203000419 | 2202031040006
 25001203000420 | 2202031040006
 25001203000421 | 2202031040006
 25001203000422 | 2202031040006
 25001203000536 | 2202031040006

and
 select trn_id,substr(hoa_id,0,14) from  tbill_master where
substr(hoa
_id,0,14)='2202031040006';
     trn_id     |    substr
----------------+---------------
 25001203000416 | 2202031040006
 25001203000417 | 2202031040006
 25001203000418 | 2202031040006
 25001203000419 | 2202031040006
 25001203000420 | 2202031040006
 25001203000421 | 2202031040006
 25001203000422 | 2202031040006
 25001203000536 | 2202031040006
both give the same result how? Is it query problem or
postgress substring problem please clarify.

Re: substring problem

From
Tom Lane
Date:
Sh A Guru Prasad <guru@ap.nic.in> writes:
>  [ substr(x,1,13) is the same as substr(x,0,14) ]

This is not a bug, this is per SQL99 spec.

            regards, tom lane

Re: substring problem

From
CoL
Date:
Hi,

Sh A Guru Prasad wrote, On 12/31/2003 11:00 AM:
>  select trn_id,substr(hoa_id,1,13) from  tbill_master where
> substr(hoa
> _id,1,13)='2202031040006';
>      trn_id     |    substr
> ----------------+---------------
>  25001203000416 | 2202031040006
>  25001203000417 | 2202031040006
>  25001203000418 | 2202031040006
>  25001203000419 | 2202031040006
>  25001203000420 | 2202031040006
>  25001203000421 | 2202031040006
>  25001203000422 | 2202031040006
>  25001203000536 | 2202031040006
>
> and
>  select trn_id,substr(hoa_id,0,14) from  tbill_master where
> substr(hoa
> _id,0,14)='2202031040006';
>      trn_id     |    substr
> ----------------+---------------
>  25001203000416 | 2202031040006
>  25001203000417 | 2202031040006
>  25001203000418 | 2202031040006
>  25001203000419 | 2202031040006
>  25001203000420 | 2202031040006
>  25001203000421 | 2202031040006
>  25001203000422 | 2202031040006
>  25001203000536 | 2202031040006
> both give the same result how? Is it query problem or
> postgress substring problem please clarify.

why? it's ok. The first position in string is 1, but you can use 0,
-100, or whatever. The manual says:
"substring(string [from integer] [for integer])",as you see: from
integer, for integer.
SELECT substr('1234',-1,3); = 1
SELECT substr('1234',1,1); = 1

this is the expected behaviour.

C.