how to compare 5-10 chars from 2 tables.

[kandersen]

Hi

I'm trying to to make a query where to compare the address listing,
i've done the compare in Excel with the first 5 characters, works fine, but I want to do it in sql to.

Example.

table 1:
PhoneNumber, Address

table 2:
PhoneNumber, Address, StreetNumber

select table1.phonenum ber, table1.address, table2.phonenum ber,address,str eetnumber

form table1, table2

where "the first 5 chars of table2.address != the first 5 chars of table1.address"


Ive tried to use like and % but that doesn't work :)...

Question number 2.

Besides the above, I need to get a listing of:
where table2.phonenum ber does not have any matches in table1.phonenum ber


Thanks in advance for any help...

Karl

[siva538]

Hi

I'm trying to to make a query where to compare the address listing,
i've done the compare in Excel with the first 5 characters, works fine, but I want to do it in sql to.

Example.

table 1:
PhoneNumber, Address

table 2:
PhoneNumber, Address, StreetNumber

select table1.phonenum ber, table1.address, table2.phonenum ber,address,str eetnumber

form table1, table2

where "the first 5 chars of table2.address != the first 5 chars of table1.address"


Ive tried to use like and % but that doesn't work :)...

Question number 2.

Besides the above, I need to get a listing of:
where table2.phonenum ber does not have any matches in table1.phonenum ber


Thanks in advance for any help...

Karl

Hi Karl,
you can accomplish the same using substring function provided by sql.

create table emp(id int, name varchar(100)) 

insert into emp values(1, 'abc1eabcd1easdfasfasd')
insert into emp values(2, 'aaaaeabcd1eaffffffffffasd')
insert into emp values(3, '111aeabc1d1aaaaaeaffffffffffasd')

select * from emp e1 with (nolock)
where substring(e1.name, 5, 5) = 'eabcd'

substring (string_to_comp are, starting point, length)

I provided a bad example .. sorry for that ..

HTH..

Regards,
Sivakumar