SQL Query using string condition

My table is as follows and I have a string ‘U2’,’U4’

CustomerID UserID OtherUsers
C1 U1 #U3~#U5~
C2 U2
C3 U3
C4 U4 #U1~#U2~
C5 U5
C6 U1 #U2~#U5~
C7 U2
C8 U3 #U1~#U4~

Now I should issue a sql which should extract the CustomerID where UserID in (‘U2’,’U4’) or (U2 or U4) in OtherUsers.
i.e I should get the CustomerIDs C2,C4,C6,C8

I have trouble in coming out with the sql. Does anyone have idea how to achieve this?

Try this:

SELECT * FROM Table
WHERE ((UserID in ('U2','U4'))
OR (PATINDEX('%'+'U2'+'%',OtherUsers) > 0)
OR (PATINDEX('%'+'U4'+'%',OtherUsers) > 0))

Patindex compares string a to string b and returns the number of times string a is in string b. % is a wildcard.

Hi,

Thanks for ur response.

The problem is, the string is dynamically generated i.e it can have any number of users in it according to some selection.

How to get the customer without hardcoding the values

Redesign the database. Read up on normalization and one to many relationships.

Thanks swampBoogie,

I have redesigned my database.

Regards
SS