Thread: string functions

string functions

From
"Michael Fuchs"
Date:
hi there!
i've got some email adresses in my db, but i just want everything before
the '@' to be returned.
example:

firstname.lastname@mydomain.tld -> firstname.lastname

what must my select statement look like?

i tried something like
"SELECT TRIM(leading '@' from email) FROM ..." needless to say that this
doesn't work.

thanks in advance!

PS: looking at "user-7.2-A4.pdf" and "user-7.2-US.pdf" in
ftp://ftp.at.postgresql.org/db/www.postgresql.org/pub/doc/current/ i had
to find out that many examples cannot be read because of too narrow
columns. are there other versions of these docs available (html, rtf,
...)?

--michael


Re: string functions

From
"Joshua b. Jore"
Date:
First let's assume that your universe of e-mail addresses don't encompass
common usage (just a sample and enough to illustrate that address parsing
requires a full-on parser and not just a simple match against _%@_%._%.

RFC 821 compliant machine part only
"Atom Atom/Atom/Atom"@domain.domain
Atom@domain.domain
Atom@domain

This is what actually appears and isn't always spec compliant
<machine_address>
<machine_address> "Text Text"
<machine_address> "Text (address@domain.domain) Text"
<machine_address> (comment comment address@domain.domain)
"Text Text" machine_address
"Text (address@domain.domain) Text" machine_address

Anyhow the expression:

SELECT substr(email,1,strpos(email,'@')) FROM ...

Joshua b. Jore ; http://www.greentechnologist.org

On Tue, 11 Jun 2002, Michael Fuchs wrote:

> hi there!
> i've got some email adresses in my db, but i just want everything before
> the '@' to be returned.
> example:
>
> firstname.lastname@mydomain.tld -> firstname.lastname
>
> what must my select statement look like?
>
> i tried something like
> "SELECT TRIM(leading '@' from email) FROM ..." needless to say that this
> doesn't work.
>
> thanks in advance!
>
> PS: looking at "user-7.2-A4.pdf" and "user-7.2-US.pdf" in
> ftp://ftp.at.postgresql.org/db/www.postgresql.org/pub/doc/current/ i had
> to find out that many examples cannot be read because of too narrow
> columns. are there other versions of these docs available (html, rtf,
> ...)?
>
> --michael
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>