pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedsubstring
22+ messages / 17 participants
[nested] [flat]
* substring
@ 1999-04-26 14:57 Nuchanard Chiannilkulchai <nuch@valigene.com>
0 siblings, 0 replies; 22+ messages in thread
From: Nuchanard Chiannilkulchai @ 1999-04-26 14:57 UTC (permalink / raw)
To: pgsql-sql@hub.org
Hello,
How should I do my query to put a substring value in a field, in
postgres ( I have 6.4) ?
I have in table table_a
a
----------------
98-004_c136
P124_154
98-005_c171
and i want to select only the text before the underscore:
in sybase, this should be
select a, substring(a,1,charindex('_',a)-1) from table_a
a
---------------- ----------------
98-004_c136 98-004
98-005_c171 98-005
P124_154 P124
Thanks a lot,
Nuch
^ permalink raw reply [nested|flat] 22+ messages in thread
* RE: [SQL] substring
@ 1999-04-26 19:35 Michael J Davis <michael.j.davis@tvguide.com>
1999-04-27 08:15 ` Re: [SQL] substring Nuchanard Chiannilkulchai <nuch@valigene.com>
0 siblings, 1 reply; 22+ messages in thread
From: Michael J Davis @ 1999-04-26 19:35 UTC (permalink / raw)
To: 'Nuchanard Chiannilkulchai' <nuch@valigene.com>; pgsql-sql@hub.org
try:
select a, substring(a, 1, position('_' in a) -1) from table_a;
I have not tested this. Not sure if the parameters to substring are
correct. Also not sure if the -1 is needed.
> -----Original Message-----
> From: Nuchanard Chiannilkulchai [SMTP:nuch@valigene.com]
> Sent: Monday, April 26, 1999 8:57 AM
> To: pgsql-sql@hub.org
> Subject: [SQL] substring
>
> Hello,
>
> How should I do my query to put a substring value in a field, in
> postgres ( I have 6.4) ?
>
> I have in table table_a
> a
> ----------------
> 98-004_c136
> P124_154
> 98-005_c171
>
> and i want to select only the text before the underscore:
>
> in sybase, this should be
> select a, substring(a,1,charindex('_',a)-1) from table_a
> a
> ---------------- ----------------
> 98-004_c136 98-004
> 98-005_c171 98-005
> P124_154 P124
>
> Thanks a lot,
> Nuch
>
>
>
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: [SQL] substring
1999-04-26 19:35 RE: [SQL] substring Michael J Davis <michael.j.davis@tvguide.com>
@ 1999-04-27 08:15 ` Nuchanard Chiannilkulchai <nuch@valigene.com>
1999-04-27 13:01 ` Re: [SQL] substring José Soares <jose@sferacarta.com>
1999-04-27 14:36 ` Re: [SQL] substring Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 22+ messages in thread
From: Nuchanard Chiannilkulchai @ 1999-04-27 08:15 UTC (permalink / raw)
To: pgsql-sql@hub.org
The problem is : select position ('_' in a ) from table_a does not
work.
ERROR: No such function 'strpos' with the specified attributes
while select position('_' in '98-004_c136') ; give the right answer.
I wonder how to introduce the attribute , and not the constant value.
Michael J Davis wrote:
> try:
>
> select a, substring(a, 1, position('_' in a) -1) from table_a;
>
> I have not tested this. Not sure if the parameters to substring are
> correct. Also not sure if the -1 is needed.
>
> > -----Original Message-----
> > From: Nuchanard Chiannilkulchai [SMTP:nuch@valigene.com]
> > Sent: Monday, April 26, 1999 8:57 AM
> > To: pgsql-sql@hub.org
> > Subject: [SQL] substring
> >
> > Hello,
> >
> > How should I do my query to put a substring value in a field, in
> > postgres ( I have 6.4) ?
> > [snips]
> > in sybase, this should be
> > select a, substring(a,1,charindex('_',a)-1) from table_a
> > a
> > ---------------- ----------------
> > 98-004_c136 98-004
> > 98-005_c171 98-005
> > P124_154 P124
> >
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: [SQL] substring
1999-04-26 19:35 RE: [SQL] substring Michael J Davis <michael.j.davis@tvguide.com>
1999-04-27 08:15 ` Re: [SQL] substring Nuchanard Chiannilkulchai <nuch@valigene.com>
@ 1999-04-27 13:01 ` José Soares <jose@sferacarta.com>
1 sibling, 0 replies; 22+ messages in thread
From: José Soares @ 1999-04-27 13:01 UTC (permalink / raw)
To: Nuchanard Chiannilkulchai <nuch@valigene.com>; +Cc: pgsql-sql@hub.org
Nuchanard Chiannilkulchai ha scritto:
> The problem is : select position ('_' in a ) from table_a does not
> work.
> ERROR: No such function 'strpos' with the specified attributes
>
> while select position('_' in '98-004_c136') ; give the right answer.
>
> I wonder how to introduce the attribute , and not the constant value.
>
> Michael J Davis wrote:
>
> > try:
> >
> > select a, substring(a, 1, position('_' in a) -1) from table_a;
> >
> > I have not tested this. Not sure if the parameters to substring are
> > correct. Also not sure if the -1 is needed.
> >
> > > -----Original Message-----
> > > From: Nuchanard Chiannilkulchai [SMTP:nuch@valigene.com]
> > > Sent: Monday, April 26, 1999 8:57 AM
> > > To: pgsql-sql@hub.org
> > > Subject: [SQL] substring
> > >
> > > Hello,
> > >
> > > How should I do my query to put a substring value in a field, in
> > > postgres ( I have 6.4) ?
> > > [snips]
>
> > > in sybase, this should be
> > > select a, substring(a,1,charindex('_',a)-1) from table_a
> > > a
> > > ---------------- ----------------
> > > 98-004_c136 98-004
> > > 98-005_c171 98-005
> > > P124_154 P124
> > >
charindex() is not SQL standard, with PostgreSQL you can
use this portable SQL standard query:
select a, substring(a from 1 for position('_' in a) - 1) as part from test;
a |part
-----------+------
98-004_c136|98-004
98-005_c171|98-005
P124_154 |P124
(3 rows)
José
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: [SQL] substring
1999-04-26 19:35 RE: [SQL] substring Michael J Davis <michael.j.davis@tvguide.com>
1999-04-27 08:15 ` Re: [SQL] substring Nuchanard Chiannilkulchai <nuch@valigene.com>
@ 1999-04-27 14:36 ` Tom Lane <tgl@sss.pgh.pa.us>
1999-04-28 12:02 ` Re: [SQL] substring Nuchanard Chiannilkulchai <nuch@valigene.com>
1 sibling, 1 reply; 22+ messages in thread
From: Tom Lane @ 1999-04-27 14:36 UTC (permalink / raw)
To: Nuchanard Chiannilkulchai <nuch@valigene.com>; +Cc: pgsql-sql@hub.org
Nuchanard Chiannilkulchai <nuch@valigene.com> writes:
> The problem is : select position ('_' in a ) from table_a does not
> work.
> ERROR: No such function 'strpos' with the specified attributes
Hmm, I don't see that under either 6.4.2 or current sources.
What data type is your column a? I tried text and char(n) and neither
one gave an error...
regards, tom lane
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: [SQL] substring
1999-04-26 19:35 RE: [SQL] substring Michael J Davis <michael.j.davis@tvguide.com>
1999-04-27 08:15 ` Re: [SQL] substring Nuchanard Chiannilkulchai <nuch@valigene.com>
1999-04-27 14:36 ` Re: [SQL] substring Tom Lane <tgl@sss.pgh.pa.us>
@ 1999-04-28 12:02 ` Nuchanard Chiannilkulchai <nuch@valigene.com>
0 siblings, 0 replies; 22+ messages in thread
From: Nuchanard Chiannilkulchai @ 1999-04-28 12:02 UTC (permalink / raw)
To: pgsql-sql@hub.org
Tom Lane wrote:
> Nuchanard Chiannilkulchai <nuch@valigene.com> writes:
> > The problem is : select position ('_' in a ) from table_a does not
> > work.
> > ERROR: No such function 'strpos' with the specified attributes
>
> Hmm, I don't see that under either 6.4.2 or current sources.
>
> What data type is your column a? I tried text and char(n) and neither
> one gave an error...
>
> regards, tom lane
the datatype of a is char(8).
select a, substring(a from 1 for position('_' in a) - 1) as part from
test;
This solution from José works great, THANX.
Nuch
^ permalink raw reply [nested|flat] 22+ messages in thread
* RE: [SQL] substring
@ 1999-04-27 14:06 Michael J Davis <michael.j.davis@tvguide.com>
1999-04-27 15:18 ` Re: [SQL] substring José Soares <jose@sferacarta.com>
0 siblings, 1 reply; 22+ messages in thread
From: Michael J Davis @ 1999-04-27 14:06 UTC (permalink / raw)
To: 'José Soares' <jose@sferacarta.com>; +Cc: pgsql-sql@hub.org
I wonder if position is a 6.5 feature? I am using 6.5.
-----Original Message-----
From: José Soares [SMTP:jose@sferacarta.com]
Sent: Tuesday, April 27, 1999 7:02 AM
To: Nuchanard Chiannilkulchai
Cc: pgsql-sql@hub.org
Subject: Re: [SQL] substring
Nuchanard Chiannilkulchai ha scritto:
The problem is : select position ('_' in a ) from table_a
does not
work.
ERROR: No such function 'strpos' with the specified
attributes
while select position('_' in '98-004_c136') ; give the
right answer.
I wonder how to introduce the attribute , and not the
constant value.
Michael J Davis wrote:
> try:
>
> select a, substring(a, 1, position('_' in a) -1) from
table_a;
>
> I have not tested this. Not sure if the parameters to
substring are
> correct. Also not sure if the -1 is needed.
>
> > -----Original Message-----
> > From: Nuchanard Chiannilkulchai [SMTP:nuch@valigene.com]
> > Sent: Monday, April 26, 1999 8:57 AM
> > To: pgsql-sql@hub.org
> > Subject: [SQL] substring
> >
> > Hello,
> >
> > How should I do my query to put a substring value in
a field, in
> > postgres ( I have 6.4) ?
> > [snips]
> > in sybase, this should be
> > select a, substring(a,1,charindex('_',a)-1) from
table_a
> > a
> > ---------------- ----------------
> > 98-004_c136 98-004
> > 98-005_c171 98-005
> > P124_154 P124
> >
charindex() is not SQL standard, with PostgreSQL you can
use this portable SQL standard query:
select a, substring(a from 1 for position('_' in a) - 1) as part
from test;
a |part
-----------+------
98-004_c136|98-004
98-005_c171|98-005
P124_154 |P124
(3 rows)
José
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: [SQL] substring
1999-04-27 14:06 RE: [SQL] substring Michael J Davis <michael.j.davis@tvguide.com>
@ 1999-04-27 15:18 ` José Soares <jose@sferacarta.com>
0 siblings, 0 replies; 22+ messages in thread
From: José Soares @ 1999-04-27 15:18 UTC (permalink / raw)
To: Michael J Davis <michael.j.davis@tvguide.com>; +Cc: pgsql-sql@hub.org
Michael J Davis ha scritto:
> I wonder if position is a 6.5 feature? I am using 6.5.
POSITION function was implemented on v6.2.
José
>
>
> -----Original Message-----
> From: José Soares [SMTP:jose@sferacarta.com]
> Sent: Tuesday, April 27, 1999 7:02 AM
> To: Nuchanard Chiannilkulchai
> Cc: pgsql-sql@hub.org
> Subject: Re: [SQL] substring
>
>
>
> Nuchanard Chiannilkulchai ha scritto:
>
> The problem is : select position ('_' in a ) from table_a
> does not
> work.
> ERROR: No such function 'strpos' with the specified
> attributes
>
> while select position('_' in '98-004_c136') ; give the
> right answer.
>
> I wonder how to introduce the attribute , and not the
> constant value.
>
> Michael J Davis wrote:
>
> > try:
> >
> > select a, substring(a, 1, position('_' in a) -1) from
> table_a;
> >
> > I have not tested this. Not sure if the parameters to
> substring are
> > correct. Also not sure if the -1 is needed.
> >
> > > -----Original Message-----
> > > From: Nuchanard Chiannilkulchai [SMTP:nuch@valigene.com]
>
> > > Sent: Monday, April 26, 1999 8:57 AM
> > > To: pgsql-sql@hub.org
> > > Subject: [SQL] substring
> > >
> > > Hello,
> > >
> > > How should I do my query to put a substring value in
> a field, in
> > > postgres ( I have 6.4) ?
> > > [snips]
>
> > > in sybase, this should be
> > > select a, substring(a,1,charindex('_',a)-1) from
> table_a
> > > a
> > > ---------------- ----------------
> > > 98-004_c136 98-004
> > > 98-005_c171 98-005
> > > P124_154 P124
> > >
>
> charindex() is not SQL standard, with PostgreSQL you can
> use this portable SQL standard query:
>
> select a, substring(a from 1 for position('_' in a) - 1) as part
> from test;
> a |part
> -----------+------
> 98-004_c136|98-004
> 98-005_c171|98-005
> P124_154 |P124
> (3 rows)
>
> José
>
^ permalink raw reply [nested|flat] 22+ messages in thread
* substring ..
@ 2000-12-19 19:06 Jeff MacDonald <jeff@hub.org>
2000-12-19 20:06 ` Re: substring .. Josh Berkus <josh@agliodbs.com>
2000-12-19 20:07 ` Re: substring .. Stephan Szabo <sszabo@megazone23.bigpanda.com>
2000-12-19 20:08 ` Re: substring .. Joel Burton <jburton@scw.org>
2000-12-19 20:13 ` Re: substring .. Tulio Oliveira <mestredosmagos@marilia.com>
2000-12-19 20:14 ` RE: substring .. Francis Solomon <francis@stellison.co.uk>
2000-12-19 21:31 ` Re: substring .. Robert B. Easter <reaster@comptechnews.com>
2000-12-19 22:12 ` Re: substring .. Jie Liang <jliang@jliang.ipinc.com>
2000-12-20 07:42 ` Re: substring .. Karel Zak <zakkr@zf.jcu.cz>
0 siblings, 8 replies; 22+ messages in thread
From: Jeff MacDonald @ 2000-12-19 19:06 UTC (permalink / raw)
To: pgsql-sql
hi folks..
i want to do this to a datetime field..
select foo from table where substr(datefoo,1,11) = '2000-12-14';
it returns no results yet..
select substr(datefoo,1,11) does return some values that say
2000-12-14
any clues ?
Jeff MacDonald,
-----------------------------------------------------
PostgreSQL Inc | Hub.Org Networking Services
jeff@pgsql.com | jeff@hub.org
www.pgsql.com | www.hub.org
1-902-542-0713 | 1-902-542-3657
-----------------------------------------------------
Facsimile : 1 902 542 5386
IRC Nick : bignose
PGP Public Key : http://bignose.hub.org/public.txt
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: substring ..
2000-12-19 19:06 substring .. Jeff MacDonald <jeff@hub.org>
@ 2000-12-19 20:06 ` Josh Berkus <josh@agliodbs.com>
7 siblings, 0 replies; 22+ messages in thread
From: Josh Berkus @ 2000-12-19 20:06 UTC (permalink / raw)
To: pgsql-sql
Jeff,
> i want to do this to a datetime field..
>
> select foo from table where substr(datefoo,1,11) = '2000-12-14';
>
> it returns no results yet..
>
> select substr(datefoo,1,11) does return some values that say
> 2000-12-14
Well, for one it's not a string, it's a datetime field.
WHy are you trying to substring a datetime field, anyway?
-Josh
--
______AGLIO DATABASE SOLUTIONS___________________________
Josh Berkus
Complete information technology josh@agliodbs.com
and data management solutions (415) 436-9166
for law firms, small businesses fax 436-0137
and non-profit organizations. pager 338-4078
San Francisco
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: substring ..
2000-12-19 19:06 substring .. Jeff MacDonald <jeff@hub.org>
@ 2000-12-19 20:07 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
7 siblings, 0 replies; 22+ messages in thread
From: Stephan Szabo @ 2000-12-19 20:07 UTC (permalink / raw)
To: Jeff MacDonald <jeff@hub.org>; +Cc: pgsql-sql
On Tue, 19 Dec 2000, Jeff MacDonald wrote:
> hi folks..
>
> i want to do this to a datetime field..
>
> select foo from table where substr(datefoo,1,11) = '2000-12-14';
>
> it returns no results yet..
>
> select substr(datefoo,1,11) does return some values that say
> 2000-12-14
>
> any clues ?
My guess is that it's a trailing space thing. '2000-12-14' is
only 10 characters not 11... What you're actually getting is
probably '2000-12-14 '
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: substring ..
2000-12-19 19:06 substring .. Jeff MacDonald <jeff@hub.org>
@ 2000-12-19 20:08 ` Joel Burton <jburton@scw.org>
7 siblings, 0 replies; 22+ messages in thread
From: Joel Burton @ 2000-12-19 20:08 UTC (permalink / raw)
To: Jeff MacDonald <jeff@hub.org>; +Cc: pgsql-sql
> i want to do this to a datetime field..
>
> select foo from table where substr(datefoo,1,11) = '2000-12-14';
>
> it returns no results yet..
>
> select substr(datefoo,1,11) does return some values that say
> 2000-12-14
Ummm... because '2000-12-14' is a ten-character, not eleven
character long string. Try substr(datefoo,1,10) and it works for me
(under 7.1devel).
However, this all seems sloppy. Why not extract the date, and
compare it as a date?
--
Joel Burton, Director of Information Systems -*- jburton@scw.org
Support Center of Washington (www.scw.org)
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: substring ..
2000-12-19 19:06 substring .. Jeff MacDonald <jeff@hub.org>
@ 2000-12-19 20:13 ` Tulio Oliveira <mestredosmagos@marilia.com>
7 siblings, 0 replies; 22+ messages in thread
From: Tulio Oliveira @ 2000-12-19 20:13 UTC (permalink / raw)
To: Jeff MacDonald <jeff@hub.org>; +Cc: pgsql-sql
Jeff MacDonald wrote:
>
> hi folks..
>
> i want to do this to a datetime field..
>
> select foo from table where substr(datefoo,1,11) = '2000-12-14';
>
> it returns no results yet..
>
> select substr(datefoo,1,11) does return some values that say
> 2000-12-14
>
> any clues ?
>
> Jeff MacDonald,
>
> -----------------------------------------------------
> PostgreSQL Inc | Hub.Org Networking Services
> jeff@pgsql.com | jeff@hub.org
> www.pgsql.com | www.hub.org
> 1-902-542-0713 | 1-902-542-3657
> -----------------------------------------------------
> Facsimile : 1 902 542 5386
> IRC Nick : bignose
> PGP Public Key : http://bignose.hub.org/public.txt
try remove the "-" from your WHERE clausule...
This signal appears only in SELECT results...
--
======================================================
AKACIA TECNOLOGIA
Desenvolvimento de sistemas para Internet
www.akacia.com.br
^ permalink raw reply [nested|flat] 22+ messages in thread
* RE: substring ..
2000-12-19 19:06 substring .. Jeff MacDonald <jeff@hub.org>
@ 2000-12-19 20:14 ` Francis Solomon <francis@stellison.co.uk>
7 siblings, 0 replies; 22+ messages in thread
From: Francis Solomon @ 2000-12-19 20:14 UTC (permalink / raw)
To: Jeff MacDonald <jeff@hub.org>; pgsql-sql
Hi Jeff,
'2000-12-14' is only 10 chars long.
You're asking for an 11-char long substring to match a 10-char ... not
going to happen!
You can see this better if you do something like this ...
select '@' || substr(datefoo,1,11) || '@' from table;
... and you'll get results like:
@2000-12-14 @
So, you could modify your query to do:
select foo from table where substr(datefoo, 1, 10) = '2000-12-14';
Alternatively, what's wrong with this approach?
select foo from table where date(datefoo) = '2000-12-14';
I think that might execute a little faster.
Hope this helps
Francis Solomon
> hi folks..
>
> i want to do this to a datetime field..
>
> select foo from table where substr(datefoo,1,11) = '2000-12-14';
>
> it returns no results yet..
>
> select substr(datefoo,1,11) does return some values that say
> 2000-12-14
>
> any clues ?
>
> Jeff MacDonald,
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: substring ..
2000-12-19 19:06 substring .. Jeff MacDonald <jeff@hub.org>
@ 2000-12-19 21:31 ` Robert B. Easter <reaster@comptechnews.com>
7 siblings, 0 replies; 22+ messages in thread
From: Robert B. Easter @ 2000-12-19 21:31 UTC (permalink / raw)
To: Jeff MacDonald <jeff@hub.org>; pgsql-sql
Hehe, here is my tests with this:
ctntest2=# SELECT create_date FROM users;
create_date
------------------------
2000-08-29 13:01:53-04
2000-08-27 20:04:41-04
2000-08-27 21:24:28-04
2000-08-30 09:51:16-04
2000-07-25 23:14:08-04
2000-07-25 23:14:08-04
2000-09-01 02:53:02-04
2000-07-25 23:14:08-04
(8 rows)
ctntest2=# SELECT substr(create_date,1,10) FROM users;
substr
------------
2000-08-29
2000-08-27
2000-08-27
2000-08-30
2000-07-25
2000-07-25
2000-09-01
2000-07-25
(8 rows)
ctntest2=# SELECT create_date FROM users WHERE substr(create_date,1,7) =
'2000-08';
create_date
------------------------
2000-08-29 13:01:53-04
2000-08-27 20:04:41-04
2000-08-27 21:24:28-04
2000-08-30 09:51:16-04
(4 rows)
Seems to work fine. My fields are TIMESTAMP. If you really still have
trouble, then try a cast: substr(datefoo::TEXT,1,10) ??
On Tuesday 19 December 2000 14:06, Jeff MacDonald wrote:
> hi folks..
>
> i want to do this to a datetime field..
>
> select foo from table where substr(datefoo,1,11) = '2000-12-14';
>
> it returns no results yet..
>
> select substr(datefoo,1,11) does return some values that say
> 2000-12-14
>
> any clues ?
>
> Jeff MacDonald,
>
> -----------------------------------------------------
> PostgreSQL Inc | Hub.Org Networking Services
> jeff@pgsql.com | jeff@hub.org
> www.pgsql.com | www.hub.org
> 1-902-542-0713 | 1-902-542-3657
> -----------------------------------------------------
> Facsimile : 1 902 542 5386
> IRC Nick : bignose
> PGP Public Key : http://bignose.hub.org/public.txt
--
-------- Robert B. Easter reaster@comptechnews.com ---------
- CompTechNews Message Board http://www.comptechnews.com/ -
- CompTechServ Tech Services http://www.comptechserv.com/ -
---------- http://www.comptechnews.com/~reaster/ ------------
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: substring ..
2000-12-19 19:06 substring .. Jeff MacDonald <jeff@hub.org>
@ 2000-12-19 22:12 ` Jie Liang <jliang@jliang.ipinc.com>
7 siblings, 0 replies; 22+ messages in thread
From: Jie Liang @ 2000-12-19 22:12 UTC (permalink / raw)
To: Jeff MacDonald <jeff@hub.org>; +Cc: pgsql-sql
Hi,there,
I am not sure what is your question mean. However,
if the type of datefoo is a timestamp then try:
select foo from table where date(datefoo) = '2000-12-14';
select foo from table where datefoo::date = '2000-12-14'::date;
select foo from table where substr(datefoo,1,10) = '2000-12-14';
might work also.
Jie LIANG
Internet Products Inc.
10350 Science Center Drive
Suite 100, San Diego, CA 92121
Office:(858)320-4873
jliang@ipinc.com
www.ipinc.com
On Tue, 19 Dec 2000, Jeff MacDonald wrote:
> hi folks..
>
> i want to do this to a datetime field..
>
> select foo from table where substr(datefoo,1,11) = '2000-12-14';
>
> it returns no results yet..
>
> select substr(datefoo,1,11) does return some values that say
> 2000-12-14
>
> any clues ?
>
> Jeff MacDonald,
>
> -----------------------------------------------------
> PostgreSQL Inc | Hub.Org Networking Services
> jeff@pgsql.com | jeff@hub.org
> www.pgsql.com | www.hub.org
> 1-902-542-0713 | 1-902-542-3657
> -----------------------------------------------------
> Facsimile : 1 902 542 5386
> IRC Nick : bignose
> PGP Public Key : http://bignose.hub.org/public.txt
>
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: substring ..
2000-12-19 19:06 substring .. Jeff MacDonald <jeff@hub.org>
@ 2000-12-20 07:42 ` Karel Zak <zakkr@zf.jcu.cz>
7 siblings, 0 replies; 22+ messages in thread
From: Karel Zak @ 2000-12-20 07:42 UTC (permalink / raw)
To: Jeff MacDonald <jeff@hub.org>; +Cc: pgsql-sql
On Tue, 19 Dec 2000, Jeff MacDonald wrote:
> hi folks..
>
> i want to do this to a datetime field..
>
> select foo from table where substr(datefoo,1,11) = '2000-12-14';
And why not to_char()?
Karel
^ permalink raw reply [nested|flat] 22+ messages in thread
* Substring
@ 2009-09-05 00:45 bilal ghayyad <bilmar_gh@yahoo.com>
2009-09-05 02:32 ` Re: Substring Raj Mathur <raju@linux-delhi.org>
0 siblings, 1 reply; 22+ messages in thread
From: bilal ghayyad @ 2009-09-05 00:45 UTC (permalink / raw)
To: pgsql-sql
Hello;
I have an sql script function that take one text parameter "funct (text)", what I need to do is the following:
If the parameter name is string and its value was for example "abcd" then I need to do a query based on ab and then based on the abc, how?
Example:
SELECT * from voipdb where prefix like string
But I need the string to be ab and then to be abc? How I can assign the string to the first character and then to the first and second? In other words, how can I can take part of the string to do query on it?
Regards
Bilal
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: Substring
2009-09-05 00:45 Substring bilal ghayyad <bilmar_gh@yahoo.com>
@ 2009-09-05 02:32 ` Raj Mathur <raju@linux-delhi.org>
2009-09-05 04:10 ` Re: Substring Frank Bax <fbax@sympatico.ca>
0 siblings, 1 reply; 22+ messages in thread
From: Raj Mathur @ 2009-09-05 02:32 UTC (permalink / raw)
To: pgsql-sql
On Saturday 05 Sep 2009, bilal ghayyad wrote:
> I have an sql script function that take one text parameter "funct
> (text)", what I need to do is the following:
>
> If the parameter name is string and its value was for example "abcd"
> then I need to do a query based on ab and then based on the abc, how?
>
> Example:
>
> SELECT * from voipdb where prefix like string
>
> But I need the string to be ab and then to be abc? How I can assign
> the string to the first character and then to the first and second?
> In other words, how can I can take part of the string to do query on
> it?
From your example the following brute-force method should work (not
tested):
select * from voipdb where prefix like substring(string from 1 for 2) ||
'%' or prefix like substring(string from 1 for 3) || '%';
However, I don't understand why you'd want to search for both 'ab' and
'abc' in the same query, since the first condition is a superset of the
second one.
Regards,
-- Raju
--
Raj Mathur raju@kandalaya.org http://kandalaya.org/
GPG: 78D4 FC67 367F 40E2 0DD5 0FEF C968 D0EF CC68 D17F
PsyTrance & Chill: http://schizoid.in/ || It is the mind that moves
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: Substring
2009-09-05 00:45 Substring bilal ghayyad <bilmar_gh@yahoo.com>
2009-09-05 02:32 ` Re: Substring Raj Mathur <raju@linux-delhi.org>
@ 2009-09-05 04:10 ` Frank Bax <fbax@sympatico.ca>
2009-09-05 04:27 ` Re: Substring Raj Mathur <raju@linux-delhi.org>
0 siblings, 1 reply; 22+ messages in thread
From: Frank Bax @ 2009-09-05 04:10 UTC (permalink / raw)
To: ; +Cc: pgsql-sql
Raj Mathur wrote:
> On Saturday 05 Sep 2009, bilal ghayyad wrote:
>> I have an sql script function that take one text parameter "funct
>> (text)", what I need to do is the following:
>>
>> If the parameter name is string and its value was for example "abcd"
>> then I need to do a query based on ab and then based on the abc, how?
>>
>> Example:
>>
>> SELECT * from voipdb where prefix like string
>>
>> But I need the string to be ab and then to be abc? How I can assign
>> the string to the first character and then to the first and second?
>> In other words, how can I can take part of the string to do query on
>> it?
>
> From your example the following brute-force method should work (not
> tested):
>
> select * from voipdb where prefix like substring(string from 1 for 2) ||
> '%' or prefix like substring(string from 1 for 3) || '%';
>
> However, I don't understand why you'd want to search for both 'ab' and
> 'abc' in the same query, since the first condition is a superset of the
> second one.
Given that tablename is "voipdb"; I wonder if OP really wants to write a
query that finds the row where argument to function matches the most
number of leading characters in "prefix".
If voipdb table contains: ab, abc, def, defg; then calling function
with "abc" or "abcd" returns "abc" and calling function with "defh"
returns "def".
If this is the real problem to be solved; then brute force is one
solution; but I'm left wondering if a single query might return desired
result (a single row).
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: Substring
2009-09-05 00:45 Substring bilal ghayyad <bilmar_gh@yahoo.com>
2009-09-05 02:32 ` Re: Substring Raj Mathur <raju@linux-delhi.org>
2009-09-05 04:10 ` Re: Substring Frank Bax <fbax@sympatico.ca>
@ 2009-09-05 04:27 ` Raj Mathur <raju@linux-delhi.org>
2009-09-07 18:36 ` Re: Substring Fernando Hevia <fhevia@ip-tel.com.ar>
0 siblings, 1 reply; 22+ messages in thread
From: Raj Mathur @ 2009-09-05 04:27 UTC (permalink / raw)
To: pgsql-sql
On Saturday 05 Sep 2009, Frank Bax wrote:
> Raj Mathur wrote:
> > On Saturday 05 Sep 2009, bilal ghayyad wrote:
> >> I have an sql script function that take one text parameter "funct
> >> (text)", what I need to do is the following:
> >>
> >> If the parameter name is string and its value was for example
> >> "abcd" then I need to do a query based on ab and then based on the
> >> abc, how?
> >>
> >> Example:
> >>
> >> SELECT * from voipdb where prefix like string
> >>
> >> But I need the string to be ab and then to be abc? How I can
> >> assign the string to the first character and then to the first and
> >> second? In other words, how can I can take part of the string to
> >> do query on it?
> >
> > From your example the following brute-force method should work (not
> > tested):
> >
> > select * from voipdb where prefix like substring(string from 1 for
> > 2) || '%' or prefix like substring(string from 1 for 3) || '%';
> >
> > However, I don't understand why you'd want to search for both 'ab'
> > and 'abc' in the same query, since the first condition is a
> > superset of the second one.
>
> Given that tablename is "voipdb"; I wonder if OP really wants to
> write a query that finds the row where argument to function matches
> the most number of leading characters in "prefix".
>
> If voipdb table contains: ab, abc, def, defg; then calling function
> with "abc" or "abcd" returns "abc" and calling function with "defh"
> returns "def".
>
> If this is the real problem to be solved; then brute force is one
> solution; but I'm left wondering if a single query might return
> desired result (a single row).
Something like this may help in that case (note, we're completely in the
realm of creating imaginary problems and solving them now :)
select * from voipdb where prefix <= string order by prefix desc limit
1;
Regards,
-- Raju
--
Raj Mathur raju@kandalaya.org http://kandalaya.org/
GPG: 78D4 FC67 367F 40E2 0DD5 0FEF C968 D0EF CC68 D17F
PsyTrance & Chill: http://schizoid.in/ || It is the mind that moves
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: Substring
2009-09-05 00:45 Substring bilal ghayyad <bilmar_gh@yahoo.com>
2009-09-05 02:32 ` Re: Substring Raj Mathur <raju@linux-delhi.org>
2009-09-05 04:10 ` Re: Substring Frank Bax <fbax@sympatico.ca>
2009-09-05 04:27 ` Re: Substring Raj Mathur <raju@linux-delhi.org>
@ 2009-09-07 18:36 ` Fernando Hevia <fhevia@ip-tel.com.ar>
0 siblings, 0 replies; 22+ messages in thread
From: Fernando Hevia @ 2009-09-07 18:36 UTC (permalink / raw)
To: raju@linux-delhi.org; pgsql-sql
> >
> > Given that tablename is "voipdb"; I wonder if OP really
> wants to write
> > a query that finds the row where argument to function
> matches the most
> > number of leading characters in "prefix".
> >
> > If voipdb table contains: ab, abc, def, defg; then calling
> function
> > with "abc" or "abcd" returns "abc" and calling function with "defh"
> > returns "def".
> >
> > If this is the real problem to be solved; then brute force is one
> > solution; but I'm left wondering if a single query might return
> > desired result (a single row).
>
> Something like this may help in that case (note, we're
> completely in the realm of creating imaginary problems and
> solving them now :)
>
> select * from voipdb where prefix <= string order by prefix
> desc limit 1;
>
> Regards,
>
> -- Raju
Hum, I wonder if some kind of best-matching query is what you are looking
for:
SELECT *
FROM voipdb
WHERE prefix IN (
SELECT substr(string, 1, i)
FROM generate_series(1, length(string)) i
);
Cheers,
Fernando.
^ permalink raw reply [nested|flat] 22+ messages in thread
end of thread, other threads:[~2009-09-07 18:36 UTC | newest]
Thread overview: 22+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1999-04-26 14:57 substring Nuchanard Chiannilkulchai <nuch@valigene.com>
1999-04-26 19:35 RE: [SQL] substring Michael J Davis <michael.j.davis@tvguide.com>
1999-04-27 08:15 ` Nuchanard Chiannilkulchai <nuch@valigene.com>
1999-04-27 13:01 ` José Soares <jose@sferacarta.com>
1999-04-27 14:36 ` Tom Lane <tgl@sss.pgh.pa.us>
1999-04-28 12:02 ` Nuchanard Chiannilkulchai <nuch@valigene.com>
1999-04-27 14:06 RE: [SQL] substring Michael J Davis <michael.j.davis@tvguide.com>
1999-04-27 15:18 ` José Soares <jose@sferacarta.com>
2000-12-19 19:06 substring .. Jeff MacDonald <jeff@hub.org>
2000-12-19 20:06 ` Re: substring .. Josh Berkus <josh@agliodbs.com>
2000-12-19 20:07 ` Re: substring .. Stephan Szabo <sszabo@megazone23.bigpanda.com>
2000-12-19 20:08 ` Re: substring .. Joel Burton <jburton@scw.org>
2000-12-19 20:13 ` Re: substring .. Tulio Oliveira <mestredosmagos@marilia.com>
2000-12-19 20:14 ` RE: substring .. Francis Solomon <francis@stellison.co.uk>
2000-12-19 21:31 ` Re: substring .. Robert B. Easter <reaster@comptechnews.com>
2000-12-19 22:12 ` Re: substring .. Jie Liang <jliang@jliang.ipinc.com>
2000-12-20 07:42 ` Re: substring .. Karel Zak <zakkr@zf.jcu.cz>
2009-09-05 00:45 Substring bilal ghayyad <bilmar_gh@yahoo.com>
2009-09-05 02:32 ` Re: Substring Raj Mathur <raju@linux-delhi.org>
2009-09-05 04:10 ` Re: Substring Frank Bax <fbax@sympatico.ca>
2009-09-05 04:27 ` Re: Substring Raj Mathur <raju@linux-delhi.org>
2009-09-07 18:36 ` Re: Substring Fernando Hevia <fhevia@ip-tel.com.ar>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox