hi hope this helps
for 50 million rows data issue = consider python/Linux ( becomes very very fast ) = moving it out of SQL Server
move the 50 million data out fo SQL Server = use python linux ( get the result set ) = move the result back into SQL Server
drop create tables insert data
-- Drop tables if they already exist
DROP TABLE IF EXISTS table_1;
DROP TABLE IF EXISTS table_2;
-- Create TABLE_1
CREATE TABLE table_1 (
col_1 VARCHAR(50)
);
-- Create TABLE_2
CREATE TABLE table_2 (
col_1 VARCHAR(50),
name VARCHAR(10)
);
-- Insert data into TABLE_1
INSERT INTO table_1 (col_1) VALUES
('123456789'),
('195678434'),
('114678900'),
('456789800'),
('112332456');
-- Insert data into TABLE_2
INSERT INTO table_2 (col_1, name) VALUES
('1', 'A'),
('12', 'B'),
('123', 'C'),
('1234', 'D'),
('12345', 'E'),
('1956', 'A'),
('195678', 'B'),
('1146', 'A'),
('11467', 'C'),
('4567', 'A'),
('112332456', 'E');
t-sql = avoiding row number
SELECT t1.col_1, x.name
FROM dbo.table_1 t1
CROSS APPLY (
SELECT TOP 1 t2.name
FROM dbo.table_2 t2
WHERE LEFT(t1.col_1, LEN(t2.col_1)) = t2.col_1
ORDER BY LEN(t2.col_1) DESC
) x;
result
