CHARINDEX(field, ‘~’ + UserID + ‘~’) > 0

That technique is sssssssssslllllllllllooooow. you can't use indexes with it. I works on small sets but you are going to get table scans. Also the length of it will constraint the design unnecessarily

I will stick to the Normalized Association table (UserID,NoteID) and create two indexes so that you could search both ways and probably  a third unique clustered on both columns that will help with range. That table will be very long but very narrow and in the end it will be very fast.

Just my $0.02