From: | John Sidney-Woollett <johnsw(at)wardbrook(dot)com> |
---|---|
To: | |
Cc: | badlydrawnbhoy <badlydrawnbhoy(at)gmail(dot)com>, pgsql-general(at)postgresql(dot)org |
Subject: | Re: Querying for strings that match after prefix |
Date: | 2006-06-02 12:17:50 |
Message-ID: | 44802C6E.7040606@wardbrook.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Or something like
select ltrim(substr(address, 8)) from people
where address like 'mailto:%'
union
select address from people
where address not like 'mailto:%'
John
John Sidney-Woollett wrote:
> Do you mean?
>
> select replace(address, 'mailto:', '') from people
>
> ... and if you only want to find the ones that start with "mailto:"
>
> select replace(address, 'mailto:', '') from people
> where address like 'mailto:%'
>
> John
>
> badlydrawnbhoy wrote:
>
>> Hi all,
>>
>> I hope this is the right forum for this, but please correct me if
>> somewhere else is more appropriate.
>>
>> I need to locate all the entries in a table that match , but only after
>> a number of characters have been ignored. I have a table of email
>> addresses, and someone else has erroneously entered some addresses
>> prefixed with 'mailto:', which I'd like to ignore.
>>
>> An example would be: john(dot)smith(at)smiths(dot)com should match
>> mailto:john(dot)smith(at)smiths(dot)com
>>
>> I've tried the following
>>
>> select address
>> from people
>> where address = (select replace(address, 'mailto:', '') from people);
>>
>> which gives me the error
>>
>> ERROR: more than one row returned by a subquery used as an expression
>>
>> I'm running on PostgreSQL 7.4.7
>>
>> Thanks in advance,
>>
>> BBB
>>
>>
>> ---------------------------(end of broadcast)---------------------------
>> TIP 6: explain analyze is your friend
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly
From | Date | Subject | |
---|---|---|---|
Next Message | badlydrawnbhoy | 2006-06-02 12:18:08 | Re: Querying for strings that match after prefix |
Previous Message | John Sidney-Woollett | 2006-06-02 12:10:19 | Re: Querying for strings that match after prefix |