Re: Querying for strings that match after prefix

John Sidney-Woollett wrote:
> 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
> 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.

Right, of course. I'd forgotten that the original query was not simply
to select the bad addresses, but to grab them all. Thanks for the
clarification.

brian