Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,554 questions
This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
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
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 ;
You may try:
SELECT * FROM @CityTable c
INNER JOIN @StateTable s
ON s.NormalizedName LIKE c.StreetName + '[a-z]%'