CODEWITHSUNDEEP
sqlsql-server
scot0308
scot0308

Reputation: 75

SQL - Join two tables on column with similar values

I am trying to join two address tables but am having trouble because they are not similar on the street name when the street is numeric. E.g. When the first table street name is equal to '2nd' the second table street name is equal to '2'. So, the tag at the end of the street is excluded from the second table. The tables have over one hundred thousand rows. If necessary I could add the tag to the end of the street, but I can't think of any good ways to do that either.

Here is my code so far:

select * from address a
inner join mai_address ma on ma.hse_nbr=a.number and a.street=ma.street

The query doesn't include any streets with numbers because of this problem.

Thanks for the help.

Upvotes: 0

Views: 2291

Answers (3)

Alisa
Alisa

Reputation: 3072

Select * 

  from address 

      inner join mai_address on dbo.f1(mai_address.hse_nbr) = address.street

And the function f1(@str) should return the left-side digits of the "@str" parameter (until reaches a non-digit or end of string). Something like this:

create function dbo.f1(@str Varchar(10)) returns varchar(10) as

  Begin 
  define @str2 varchar(10)
  define @i int
  set @str2 = ''
    set @i = 1
    while ((@i <= len(@str)) and (substring(@str, @i, @i) between '0' and '9'))
        begin
        @str2 = concat(@str2, substring(@str, @i, @i))
        set @i = @i + 1
        end
    return @str2
end

Upvotes: 1

scot0308
scot0308

Reputation: 75

Here is what I did. 

CREATE Function [fnRemoveNonNumericCharacters](@strText VARCHAR(1000))
RETURNS VARCHAR(1000)
AS
 BEGIN
  WHILE PATINDEX('%[^0-9]%', @strText) > 0
 BEGIN
    SET @strText = STUFF(@strText, PATINDEX('%[^0-9]%', @strText), 1, '')
 END
    RETURN @strText
 END

Then to join I did this:

select * from address a
inner join mai_address ma on ma.hse_nbr=a.number and (a.street=ma.street OR dbo.fnRemoveNonNumericCharacters(ma.street)=a.street)
and (ma.unit_nbr=a.apt OR (ma.unit_nbr IS NULL AND a.apt IS NULL))
order by a.number

I used the function to revise the column value so it didn't include text. I also used an or to take join on the street names that didn't include numbers.

Thanks for the great ideas!

Upvotes: 0

Slade
Slade

Reputation: 2443

The only way you're going to be able to add that kind of logic into your database is to create a function to strip out the text you don't want. However, joining two tables on a field wrapped in a function would be very inefficient.

A better solution would be to create the function but use it to update a new field in both tables and join on that table. This could be done on a trigger

ie:

create trigger dbo.address_update
on dbo.address
after insert, update
as
begin
  update address a
  set    processed_street = ProcessStreet(street)
  join   inserted i
  on     i.unique_field = a.unique_field;
end;

Upvotes: 1

Related Questions