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
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
