How to search in fields delimited by the tilde character

Question

I have a field in the database separated by tilde ~

I can't figure out how to search in the field with a mssql query.

Ex select * from table where john in repnames

Data is in the field like tom~john~max

I've tried a few searches on Google but haven't found any tips.

Answer 1

Try below :

select column1, column2
from table
where repnames like '%~%'

Note with limited info that you have provied, this is not the most efficient way as it will be scanning the table.