Comparing URLS with SQL

[columbo2]

Hello!
I'm trying to find matches between columns holding URLS in two tables.
I want to remove all http,

http://www //,:,

etc before doing the match.
The way I am doing it is by makeing a copy of each table and doing a series of replace commands on the table copies to remove all these characters before matching.
Then I do the match on the two copy tables and refer back to the main tables to get the full data (that has not been subjected to replace statements).

Does anyone know how to do this without making copies
i.e. something like

....where (table1.url)[ignoring www, http etc] = table2.url [ignoring www, http etc]

any help much appreciated.
ta very much
C

 

[r937]

whichever database you're using will likely have a Replace function

i don't think there is one in ANSI SQL (the forum you posted in)

so you want something like

where replace(
      replace(
      replace(table1.url,'[URL unfurl="true"]http://','')[/URL]
                        ,'[URL unfurl="true"]www','')[/URL]
                        ,'etc','')
    = replace(
      replace(
      replace(table2.url,'[URL unfurl="true"]http://','')[/URL]
                        ,'[URL unfurl="true"]www','')[/URL]
                        ,'etc','')

r937.com | rudy.ca

 

[columbo2]

Thanks very much
I'll give that a go.
Does that not change the table its working on or do the REPLACE statements only affect the temporary tables generated by SQL while its working?

 

[columbo2]

It doesn't affect the original data, I just tried it to answer my own question.
Thanks for your help! Much appreciated

 

[swampBoogie]

i don't think there is one in ANSI SQL (the forum you posted in)

It's called overlay in standard SQL. It has the bloated syntax

OVERLAY(<expression> PLACING <expression> 
   FROM <numeric value expression>
  [FOR <numeric value expression>])

I haven't seen any DBMS with support for this.