Query to check if two numbers share first n digits (starts with another number)

Question

Suppose that I have the following Tables:

create table #Temp1(number int)
insert into #Temp1 values (12345),(123456),(1234567)

create table #Temp2(number int)
insert into #Temp2 values (123),(123456),(2345)

select * from #Temp1
select * from #Temp2

#Temp1        #Temp2
+---------+   +---------+
|  number |   |  number |
+---------+   +---------+
|  12345  |   |   123   |
|  123456 |   |  123456 |
| 1234567 |   |   2345  |
+---------+   +---------+

I want to check if #Temp1.number starts with #Temp2.number. Something like this:

+-------------+------------+
| this number | found here |
+-------------+------------+
|    123      |    12345   |
|    123      |    123456  |
|    123      |   1234567  |
|   123456    |    123456  |
|   123456    |   1234567  |
+-------------+------------+

Note: 2345 is not found, because none of numbers in #Temp1 starts with 2345

Answer 1

Seems like you just need to convert to a varchar and then use a LIKE:

SELECT T1.number,
       T2.number
FROM #Temp1 T1
     JOIN #Temp2 T2 ON CONVERT(varchar(20),T1.number) LIKE CONVERT(varchar(20),T2.number) + '%';

Note that, due to the conversion, this will not be performant for large data sets as the query is not SARGable.