Normalize street name

Question

I have two tables containing street names
I'm trying to check the string from table A against the String from table B
and copy the missing character (always the last one)

any idea ?

--DDL and sample data population, start  
          
  DECLARE @CityTable Table (ID INT IDENTITY PRIMARY KEY, StreetName varchar(30), StnPosType varchar(20))  
  DECLARE @StateTable Table (ID INT IDENTITY PRIMARY KEY, NormalizedName varchar(30), NormStnPosType varchar(20))  
  
        
  INSERT INTO @CityTable (StreetName, StnPosType) VALUES  
  ('WEND', 'LN'),  
  ('Edgewate' , 'Ave'),  
  ('Oakhurs' , 'Dr')  
   
  INSERT INTO @StateTable (NormalizedName, NormStnPosType) VALUES  
   ('WENDY', 'LN'),  
  ('Edgewater' ,'Ave'),  
  ('Oakhursrt' , 'Dr')  
  
  Select * from @CityTable , @StateTable  
   

Answer 1

Hi @Debilon ,

Please try the following solution.

SQL

-- DDL and sample data population, start  
DECLARE @CityTable Table (ID INT IDENTITY PRIMARY KEY, StreetName varchar(30), StnPosType varchar(20));  
DECLARE @StateTable Table (ID INT IDENTITY PRIMARY KEY, NormalizedName varchar(30), NormStnPosType varchar(20));  
            
INSERT INTO @CityTable (StreetName, StnPosType) VALUES  
('WEND', 'LN'),  
('Edgewate' , 'Ave'),  
('Oakhurs' , 'Dr');  
       
INSERT INTO @StateTable (NormalizedName, NormStnPosType) VALUES  
('WENDY', 'LN'),  
('Edgewater' ,'Ave'),  
('Oakhursrt' , 'Dr');  
-- DDL and sample data population, end  
  
SELECT * FROM @CityTable AS c  
 INNER JOIN @StateTable AS s  
 ON c.StreetName = LEFT(s.NormalizedName, LEN(s.NormalizedName) - 1);  

-- more than one character  
SELECT * FROM @CityTable AS c  
OUTER APPLY (SELECT TOP(1) I.NormalizedName  
              FROM @StateTable I   
              WHERE I.NormalizedName LIKE c.StreetName + '%'  
              ORDER BY LEN(I.NormalizedName) DESC) AS I;  

UPDATE c  
SET c.StreetName = I.NormalizedName  
FROM @CityTable AS c  
OUTER APPLY (SELECT TOP(1) I.NormalizedName  
              FROM @StateTable I   
              WHERE I.NormalizedName LIKE c.StreetName + '%'  
              ORDER BY LEN(I.NormalizedName) DESC) AS I;  
  
-- test  
SELECT * FROM @CityTable ;  

Answer 2

You may try:

SELECT * FROM @CityTable  c
     INNER JOIN @StateTable s
     ON s.NormalizedName LIKE c.StreetName + '[a-z]%'