CODEWITHSUNDEEP
sqlasp.netsql-server
Maestro00
Maestro00

Reputation: 21

Search a text in a column

I have an ASP.NET page and have a table looks like

Create table Test(id int, Users Varchar(MAX));
Insert into Test select 1, 'admin;operator;user1';
Insert into Test select 2, 'superadmin';
Insert into Test select 3, 'superadmin;admin';

Any of Test row can include more than one user, so I am dividing them by semicolon. I want to return the value when the client search in textbox, they will insert only: admin I want to return only the rows which includes admin.

I can not use 

select id,Users where Users like '%admin%'

Because in this case the query will return 2nd and 3rd columns which includes superadmin.

How can I get true result?

Upvotes: 0

Views: 121

Answers (3)

Thom A
Thom A

Reputation: 95544

As I mentioned in the comment, you need to fix your design; what you're doing right now is breaking one of the fundamental Normal Form rules. That means, instead, have one row per user:

CREATE TABLE Test (uid int IDENTITY,
                   id int,
                   Username nvarchar(128));
INSERT INTO Test (id,
                  Username)
VALUES (1, N'admin'),
       (1, N'operator'),
       (1, N'user1'),
       (2, N'supermadmin'),
       (3, N'superadmin'),
       (3, N'admin');

Then you can simply use a = operator:

SELECT *
FROM Test
WHERE Username = N'admin';

Upvotes: 1

Rajat Jaiswal
Rajat Jaiswal

Reputation: 643

I guess you can try below query

SELECT * FROM #tmpTest where users like '%Admin%' AND Users not like 'superAdmin'

Upvotes: 0

HoneyBadger
HoneyBadger

Reputation: 15130

You shouldn't be storing delimited data, if possible, normalize your database. As this isn't always possible, you can get what you want with:

where CONCAT(';', Users, ';') like '%;admin;%'

Upvotes: 4

Related Questions