Re: Querying for strings that match after prefix

1) select ltrim(substr(address, 8)) from people
where address like 'mailto:%'

gives all addresses that start with "mailto:" but first strips off the
prefix leaving only the email address

2) select address from people where address not like 'mailto:%'

produces all email address that don't need the prefix stripped off

The UNION of the two gives you all the unique/distinct addresses by
combining the results from the first and second query.

John

brian ally wrote:
> John Sidney-Woollett wrote:
>
>>>> 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.
>
> >
>
>> Or something like
>>
>> select ltrim(substr(address, 8)) from people where address like
>> 'mailto:%' union select address from people where address not like
>> 'mailto:%'
>>
>
> Could you explain why the UNION?
>
> brian
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend