Query: Find string in database field

Question

In my database table, I have three fields. Let's say the fields names are name,age, and favorite_numbers.

Now, the contents of my database are as follows:

Anna is 15 and her favorite numbers are "14,21,16"
Jessica is 20 and her favorite numbers are "21,30,20"
Sara is 30 and her favorite numbers are "50,45,21"
Jessa is 12 and her favorite numbers are "10,12,20"
Farrah is 19 and her favorite numbers are "6,12,20"

How do I formulate my query in order to view the names of the people whose has the favorite number of 21?

In the contents above, I wanted to display Anna, Jessica and Sara's names only since the three of them has the favorite number of 21.

if favorite_numbers is a varchar, you'll need something like "select name from table where favorite_number like %21% — Ryan
– Ryan, Commented Oct 26, 2013 at 4:02

Adriaan Stander · Accepted Answer · 2013-10-26 04:08:22Z

3

Have a look at using FIND_IN_SET(str,strlist)

Returns a value in the range of 1 to N if the string str is in the string list strlist consisting of N substrings. A string list is a string composed of substrings separated by “,” characters. If the first argument is a constant string and the second is a column of type SET, the FIND_IN_SET() function is optimized to use bit arithmetic. Returns 0 if str is not in strlist or if strlist is the empty string. Returns NULL if either argument is NULL.

SQL Fiddle DEMO

Please note that using

WHERE nums LIKE '%21%'

will not work, as that would then also include 211 and 121

edited Oct 26, 2013 at 4:08

answered Oct 26, 2013 at 4:02

Adriaan Stander

167k32 gold badges294 silver badges288 bronze badges

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

Comments

Oki E. Rinaldi · Accepted Answer · 2013-10-26 04:02:48Z

0

Try this query :
i imagine that your table name is user (you didn't give the table name).

SELECT * FROM user WHERE favorite_numbers like '%21%'

answered Oct 26, 2013 at 4:02

Oki E. Rinaldi

1,8732 gold badges22 silver badges33 bronze badges

3 Comments

coding.bird Over a year ago

Oh i forgot the table name. Sorry,my bad. Anyway, thanks for this. I forgot the use of % again. Thanks mate (y) ;)

Adriaan Stander Over a year ago

This is not correct, as it will also include numbers like 121 and 211 etc.

Oki E. Rinaldi Over a year ago

Oh ya,, you right astander. i didn't notice it. But if the favorite_numbers is not more than 100, i think we can use it.. :D

Collectives™ on Stack Overflow

Query: Find string in database field

2 Answers 2

SQL Fiddle DEMO

Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related