Re: Querying for strings that match after prefix

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