I'm trying to find an exact string within a list of other strings on an SQL Server. The first string could partially be part of another string. For example, looking for 123 within 123 and 01230 only returning the first row as its an exact match. Not returning the string 01230. The original string could be of varying length, so I could be searching for 123, or I could be search for 1230. Further example below:

Say, I have a column of strings like:

_VB12345.VB84556
_VB1234.VB84556
_VB12345.VB8455
_VB8455.VB1234

what query would I write to find:

VB1234 - returning the 2nd and 4th value VB8455 - returning the 3rd and 4th value

I cannot just write:

select *
from table
where reference like '%VB1234%'

As this would return all 4 rows.

I also cannot write:

select *
from table
where reference like '%VB1234.%'

As this would return the second row only. I'm not sure how to include special characters as the string terminator.

I hope this makes enough sense to people