I'm trying to compare two columns from two separate tables (Table A, Table B). The column in B is equal to the column in table A and it may have extra digits afterwards.
Example:
A.Col1 = 95792313
B.Col1 = 9579231300
Which means that B.Col1 = '%' + A.Col +'%'
A has around 3 million records and B has 15 million. How can I utilize regular expression in SQL in order to achieve that?
How about something like this....
declare @a bigint = 95792313
declare @b bigint = 9579231300
select 1 where @a like left(@b,len(@a))
I'm just truncating B.Col1, which is @b in this case, to the length of A.Col1 which is @a in this case
So for you, something like
WHERE A.COL1 LIKE LEFT(B.Col1,LEN(A.Col1))
As your approach to compare these values is not a numerical one, but rather character based, the easiest would be to compare just A.Col1 with a snippet of the same length cut from the beginning of B.Col1.
Try this:
DECLARE @tblA TABLE(Col1 BIGINT);
DECLARE @tblB TABLE(Col1 BIGINT);
INSERT INTO @tblA VALUES(123),(1234),(12345);
INSERT INTO @tblB VALUES(12300),(12340000),(1345);
SELECT A.Col1
,B.Col1
,LEN(A.Col1)
,CASE WHEN A.Col1=LEFT(B.Col1,LEN(A.Col1)) THEN 'Start with the same digits' ELSE '' END
FROM @tblA AS A
CROSS JOIN @tblB AS B
The result
+----------+----------+--------------------+----------------------------+
| Col1 | Col1 | (Kein Spaltenname) | (Kein Spaltenname) |
+----------+----------+--------------------+----------------------------+
| 123 | 12300 | 3 | Start with the same digits |
+----------+----------+--------------------+----------------------------+
| 1234 | 12300 | 4 | |
+----------+----------+--------------------+----------------------------+
| 12345000 | 12300 | 8 | |
+----------+----------+--------------------+----------------------------+
| 123 | 12340000 | 3 | Start with the same digits |
+----------+----------+--------------------+----------------------------+
| 1234 | 12340000 | 4 | Start with the same digits |
+----------+----------+--------------------+----------------------------+
| 12345000 | 12340000 | 8 | |
+----------+----------+--------------------+----------------------------+
| 123 | 1345 | 3 | |
+----------+----------+--------------------+----------------------------+
| 1234 | 1345 | 4 | |
+----------+----------+--------------------+----------------------------+
| 12345000 | 1345 | 8 | |
+----------+----------+--------------------+----------------------------+
A CROSS JOIN with millions of rows in both tables is no good idea. This was just to illustrate the approach. You might use an INNER JOIN and put this code as the join's condition:
SELECT A.Col1
,B.Col1
,LEN(A.Col1)
,CASE WHEN A.Col1=LEFT(B.Col1,LEN(A.Col1)) THEN 'Start with the same digits' ELSE '' END
FROM @tblA AS A
INNER JOIN @tblB AS B ON A.Col1=LEFT(B.Col1,LEN(A.Col1))
WHERE A.Col1=LEFT(B.Col1,LEN(A.Col1)) at the end of the query to put a filter on the set.CROSS JOIN with multi-million rows is no good idea. See my update in a minute!
^(\d+)[ ]\1to select.