CODEWITHSUNDEEP
sqldjango
user3361761
user3361761

Reputation: 259

How can I perform this query?

One of the fields in my database is a string containing multiple usernames separated by commas (ex: 'wasingej, doej, hamm, ..."). I want to do a query where a database entry (or multiple) is selected if a supplied username appears in the string containing multiple usernames.

How would I do this?

Upvotes: 0

Views: 54

Answers (2)

Raging Bull
Raging Bull

Reputation: 18767

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.

Upvotes: 0

erik258
erik258

Reputation: 16302

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

Upvotes: 1

Related Questions