What happens when the main table contains BS1234 as well as BS12345 ?

As ugly as it might perform, you may need to join like this:

SELECT fld1, fld2, fld3

FROM VISITS AS V

LEFT JOIN REVISITS AS R

ON V.Reference = LEFT(R.Reference, CHARINDEX('R', R.Reference) - 1)