question

Debilon-3192 avatar image
0 Votes"
Debilon-3192 asked YitzhakKhabinsky-0887 edited

Normalize street name

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



sql-server-transact-sql
image.png (12.3 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

YitzhakKhabinsky-0887 avatar image
0 Votes"
YitzhakKhabinsky-0887 answered YitzhakKhabinsky-0887 edited

Hi @Debilon-3192,

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 ;
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

NaomiNNN avatar image
0 Votes"
NaomiNNN answered

You may try:

 SELECT * FROM @CityTable  c
      INNER JOIN @StateTable s
      ON s.NormalizedName LIKE c.StreetName + '[a-z]%'
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.