[Answer]-How can I perform this query?

The best I could come up with is

CREATE TABLE supportContacts 
    (
     id int identity primary key, 
     usernames varchar(255), 
    );

INSERT INTO supportContacts
(usernames)
VALUES
('Dan'),
('DAN,SCOTT'),
('Jordan,Michael,Dan'),
('Adam,Kelly,Daniel,Roger,Sue'),
('Crystal,Kari,Logan,Daniella'),
('Jon,Dan,Xerxes,Brian'),
('Samantha,Keylin,Dan')
;
SELECT * from supportContacts WHERE 
  usernames = 'Dan' or -- only dan
   usernames LIKE 'Dan,%' or  -- begins
  usernames LIKE ',Dan,' or -- within
  usernames LIKE '%,Dan' -- ends

It has the problem that it doesn’t match on case – not an issue if your input isn’t case sensitive anyway – but it is Better than Raging Bull’s answer because it doesn’t match the names within other names ( my fiddle shows this by not matching ‘Daniella’ or ‘Daniel’ )

http://sqlfiddle.com/#!6/72493/4

USING LIKE:

DECLARE @CONDITION VARCHAR(255)
SET @CONDITION = '%'' UNION SELECT * FROM TableName WHERE ColName LIKE ''%'

DECLARE @Unames VARCHAR(255)       --List of username you pass from your program
SET @Unames = 'wasingej,doej,hamm' --Taking it as an example

DECLARE @FullQuery VARCHAR(255)
SET @FullQuery = 'SELECT *
  FROM TableName
 WHERE ColName LIKE ''%'+REPLACE(@Unames,',',@CONDITION)+'%'''  --Replacing comma with @Condition

EXEC(@FullQuery)

At last, you can get your query in the variable @FullQuery like this:

SELECT * FROM TableName WHERE ColName LIKE '%wasingej%' 
UNION 
SELECT * FROM TableName WHERE ColName LIKE '%doej%' 
UNION 
SELECT * FROM TableName WHERE ColName LIKE '%hamm%'

See example in SQL Fiddle.