comparing string in database field

Question

hello i have table with the column name id,receiver_id and many more now i want to fetch record in which sender_id is current_logged in user and receiver_id contains the current loggend in id.

means receiver_id contains the string of user_id separated with , now i want to check whether user_id there in receiver_id or not

suppose my id is U112 and receiver_id contains string U112,U123,U157 now how can i check

i tried as follow but fails

$selDoc="SELECT * FROM documents WHERE sender_id='U112' OR 'U112' IN (receiver_id) ORDER BY id DESC";

you should probably normalise your schema.. this is going to be a full table scan — Karoly Horvath
– Karoly Horvath, Commented Apr 23, 2013 at 10:42

Karoly Horvath · Accepted Answer · 2013-04-23 10:47:09Z

3

str IN (str2) is the same as str == str2.

Use FIND_IN_SET:

FIND_IN_SET('U112', receiver_id)

Note: you should probably normalize your schema.. this is going to be a full table scan.

answered Apr 23, 2013 at 10:47

Karoly Horvath

96.7k11 gold badges123 silver badges181 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Yadav Chetan Over a year ago

i cant change database because it already live and running on server

Karoly Horvath Over a year ago

well, at some point you have to learn how to update a production database.. schemas aren't written in stone ;)

Yadav Chetan Over a year ago

but i am not authorized to do that

enigmaticus · Accepted Answer · 2013-04-23 10:54:01Z

0

or you can use

receiver_id LIKE "% U112 %"

answered Apr 23, 2013 at 10:54

enigmaticus

5583 gold badges8 silver badges26 bronze badges

1 Comment

enigmaticus Over a year ago

i thought that it is maybe fixed with 3 numbers after letter, sorry my bad :(

Vijaya Pandey · Accepted Answer · 2013-04-23 11:08:46Z

0

This might work correctly:

SELECT * FROM documents WHERE sender_id in ('U112','U113','U113')

OR

SELECT * FROM documents WHERE sender_id ='U112' and sender_id in ('U112','U113','U113')

Note: I worked on my own table and it worked perfectly. Example:

select * from tbl_answers where ques_id in ('69','12','14')
select * from tbl_answers where ques_id = 69 and ques_id in ('69','12','14')

answered Apr 23, 2013 at 11:08

Vijaya Pandey

4,2705 gold badges35 silver badges58 bronze badges

Comments

Ren · Accepted Answer · 2013-04-23 11:40:15Z

0

You can try like this:

SELECT  * FROM documents WHERE (receiver_id LIKE '%,U112' OR receiver_id LIKE 'U112,%' OR receiver_id LIKE '%,U112,%' OR receiver_id LIKE 'U112')

edited Apr 23, 2013 at 11:40

Ren

1,1095 gold badges15 silver badges24 bronze badges

answered Apr 23, 2013 at 11:14

sankarji

1

Collectives™ on Stack Overflow

comparing string in database field

4 Answers 4

3 Comments

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

3 Comments

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related