OT: Help with SQL SELECT on Varchar from character 0..n
Hi, I'm using postgres and remember having done something ages ago with some operator in mysql, although it makes zero sense to me now as to how I did it the first time. What I want to do is have a select return a partial string from the ith character of a string to the jth character of a string. I'd like postgres to filter this partial string and return it to me. Any ideas how I can go about doing this without using postgres regular expressions? Am I simply imagining that I managed to do this the first time around? Or should I be ashamed that I don't remember how to do it this time around? Cheers, Rafiq
Re: OT: Help with SQL SELECT on Varchar from character 0..n
Doh! SUBSTR - I forgot to wear my brain this morning. On Tue, 17 Sep 2002, Rafiq Ismail (ADMIN) wrote: Hi, I'm using postgres and remember having done something ages ago with some operator in mysql, although it makes zero sense to me now as to how I did it the first time. What I want to do is have a select return a partial string from the ith character of a string to the jth character of a string. I'd like postgres to filter this partial string and return it to me. Any ideas how I can go about doing this without using postgres regular expressions? Am I simply imagining that I managed to do this the first time around? Or should I be ashamed that I don't remember how to do it this time around? Cheers, Rafiq
Re: OT: Help with SQL SELECT on Varchar from character 0..n
On Tue, Sep 17, 2002 at 09:34:08AM +, Rafiq Ismail (ADMIN) wrote:
I'm using postgres and remember having done something ages ago with some
operator in mysql, although it makes zero sense to me now as to how I did
it the first time. What I want to do is have a select return a partial
string from the ith character of a string to the jth character of a
string. I'd like postgres to filter this partial string and return it to
me.
Not completed but do you mean something along the lines of:
create table FOO ( content text );
insert into foo values ('I\'m using postgres and remember having done something ages
ago with some');
insert into foo values ('operator in mysql, although it makes zero sense to me now as
to how I did');
insert into foo values ('it the first time. What I want to do is have a select return
a partial');
insert into foo values ('string from the ith character of a string to the jth
character of a');
insert into foo values ('string. I\'d like postgres to filter this partial string and
return it to');
insert into foo values ('me.');
select substring(content from 11 for 4) from FOO;
and you'd like a where clause on the 4 characters?
Chisel
--
e: [EMAIL PROTECTED] | Stick with what you know and travel
w: www.herlpacker.co.uk | light; if you only carry a hammer then
gpg: D167E7FE | all problems are nails.
Re: OT: Help with SQL SELECT on Varchar from character 0..n
On Tue, Sep 17, 2002 at 11:04:53AM +, Rafiq Ismail (ADMIN) wrote: Kind of typical, it came back to me after posting. select substring(content from 11 for 4) from FOO; and you'd like a where clause on the 4 characters? thus : SELECT substr(content,11, 15) AS fish FROM FOO where fish='fish' That didn't work when I tried it: chisel=# select substring(content from 11 for 4) as fish from FOO where fish = 'post'; ERROR: Attribute 'fish' not found chisel=# select substring(content from 11 for 4) as fish from FOO where substring(content from 11 for 4) = 'post'; +--+ | fish | +--+ | post | +--+ (1 row) Chisel -- e: [EMAIL PROTECTED] | Buy 'em books, and they eat the w: www.herlpacker.co.uk | fscking covers. gpg: D167E7FE |
