CODEWITHSUNDEEP
mysqlsqlstring
bfavaretto
bfavaretto

Reputation: 71939

Is there a FIND_IN_SET by index in MySQL?

I know storing lists as strings is not wise, but I have to deal with that to export some data stored that way. I also know the FIND_IN_SET function, which returns the index of a certain string in a list:

SELECT FIND_IN_SET('bar', 'foo,bar,baz');
-- selects 2

Is there any built-in function (or combination of functions) to get the string in a particular index of the list? I'm looking for something like this:

SELECT IN_SET_AT_INDEX(2, 'foo,bar,baz');
-- selects 'bar'

I'd like to avoid a split-like function that makes the list a separate table, if possible.

Upvotes: 3

Views: 3267

Answers (2)

Bill Karwin
Bill Karwin

Reputation: 562731

SUBSTRING_INDEX() can do this, sort of:

mysql> SELECT SUBSTRING_INDEX(SUBSTRING_INDEX('foo,bar,baz', ',', 2), ',', -1) AS middle_one;
+------------+
| middle_one |
+------------+
| bar        |
+------------+

Upvotes: 9

Gordon Linoff
Gordon Linoff

Reputation: 1270633

Yes, but it requires just a little trickery. The basis is substring_index(), but that gets everything up to the nth entry. Then I use reverse() twice and another substring_index():

select reverse(substring_index(reverse(substring_index('foo,bar,baz', ',', 2)), ',', 1))

In your case, the transformations are:

original string:                'foo,bar,baz'
after substring_index(..., 2)   'foo,bar'
after inner reverse             'rab,oof'
after substring_index(..., 1)   'rab'
after outer reverse             'bar'

Upvotes: 1

Related Questions