MySQL Selecting Something with Array Information

Question

Is there a way to do something like this?

SELECT * FROM tablename WHERE x CONTAINS "1"

Basically, I want to select data from the database where x contains a specific number. The thing is, the x column in any row could contain "1, 2, 3" and I want to select all those that contain 1, specifically 1, not 11 or anything that contains 1, but specifically a 1.

Here's an example:

id   title   x
-------------------
1    row1    1,22,3
2    row2    1,5
3    row3    5,91
4    row4    70

And I want my query to return rows 1 and 2. I don't want row 3, as the 1 is inside the number 91. I don't want row 4 because there's no 1 there either.

Separate x values to a different table, then use joins. This way you're just shooting yourself in the leg. — Tatu Ulmanen
– Tatu Ulmanen, Commented Jul 27, 2011 at 8:36

a'r · Accepted Answer · 2011-07-27 08:36:58Z

2

You can use the FIND_IN_SET function like so:

SELECT * FROM tablename WHERE FIND_IN_SET('1', x)

This will also get optimised to use bit arithmetic if you are calling it on a SET type.

answered Jul 27, 2011 at 8:36

a'r

37.2k7 gold badges68 silver badges67 bronze badges

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

Comments

lazyhammer · Accepted Answer · 2011-07-27 08:36:05Z

1

You can try this:

SELECT * FROM tablename WHERE x REGEXP "(^|,)1(,|$)"

answered Jul 27, 2011 at 8:36

lazyhammer

1,2281 gold badge11 silver badges14 bronze badges

Comments

Ash Eldritch · Accepted Answer · 2011-07-27 08:40:34Z

1

Ideally you'd normalize your 'x' column out to a separate table.

But... you could also hack it like this:

SELECT * FROM tablename WHERE x LIKE '%,1' OR x LIKE '1,%' OR x LIKE '%,1,%'

This basically just handles the three different cases where the "1" is the first, last or a middle element in your list. (note if you've got a space after your commas you'd change the last part to '%, 1,%'

EDIT: Actually Dmitriy's REGEXP is nicer, and a'r's FIND_IN_SET looks ideal.

edited Jul 27, 2011 at 8:40

answered Jul 27, 2011 at 8:35

Ash Eldritch

1,51410 silver badges14 bronze badges

1 Comment

a'r Over a year ago

It's easier to do this: CONCAT(',', x, ',') LIKE '%,1,%'

Collectives™ on Stack Overflow

MySQL Selecting Something with Array Information

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related