Normalize street name

Debilon 431 Reputation points
2022-05-04T17:09:56.427+00:00

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  
   

198914-image.png

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,599 questions
0 comments No comments
{count} votes

Accepted answer
  1. Yitzhak Khabinsky 25,731 Reputation points
    2022-05-04T18:14:22.583+00:00

    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 ;  
    
    0 comments No comments

1 additional answer

Sort by: Most helpful
  1. Naomi 7,366 Reputation points
    2022-05-04T18:27:38.883+00:00

    You may try:

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