sql - Joining tables in a database using differently formated data (MAC addresses) -
i have 2 tables i'd join via mac address field, each table stores mac addresses different: table 1 data: 0:1e:8:c5:9e:fe table 2 data: 00:1e:08:c5:9e:fe the first 1 removes starting 0's of 6 groups of colon-separated fields. is there way can join on these in sql without having modify data? i'm guessing have convert both values xx:xx:xx:xx:xx:xx, compare them...i'm not quite sure how that. you can remove leading zeroes replace , stuff functions: select * table1 inner join table2 on table1.shortmac = stuff(replace(':' + table2.longmac, ':0', ':'), 1, 1, '')