CODEWITHSUNDEEP
mysqlsqlselectsubstring
Alan N
Alan N

Reputation: 191

MySQL - Select substring from a column, without catching similar substrings from same column

In a MySQL table I have a VARCHAR column called ShareID.

I would like to grab all the rows where the ShareID is 1. i.e. ONLY the first and second rows here.

I have tried using the LIKE command, like so: 

SELECT * FROM tablename WHERE ShareWithID LIKE '1%';

but this will catch ALL the rows that contain the number 1 in it, i.e. Row #3 which is not what I want.

I would like to run a command that would ONLY return rows #1 and #2 above because they have a ShareID of 1 contained within it.

I've tried a variety of commands, (including REGEXP, and IN) and managed a 'frig' solution where I'd place a comma after EVERY number in the ShareID column, including the last one (i.e. 10, 1,), and then execute this command:

SELECT * FROM tablename WHERE ShareWithID LIKE '%1,%';

But I would rather use a proper solution over a frigged solution.

Any guidance would be most welcome.

Upvotes: 0

Views: 789

Answers (3)

Bill Karwin
Bill Karwin

Reputation: 563021

...the built-in feature is there to be used

FIND_IN_SET() is actually not intended to be used for strings containing comma-separated lists. It's intended to be used with MySQL's SET data type. Hence the name FIND_IN_SET(), not FIND_IN_COMMA_SEPARATED_LIST().

It saves having to waste time building a 250,000 row 'table' (was it??) to look after a few columns of IDs, when one column in the original 'table' could do the job just as well.

250k rows is not a problem for MySQL. I manage databases with billions of rows in a given table. If you do basic query optimization with indexes, most queries on a table of 250k rows are just fine.

Whereas using a comma-separated list, you spoil any chance of optimizing queries. An index does not help searching for substrings that may not be the leftmost prefix of the string, and searching for a number in a comma-separated list is basically searching for a substring.

You're making your queries impossible to optimize by using a comma-separated list. Every query using FIND_IN_SET() will be a table-scan, which will get slower in a linear relationship to the number of rows in your table.

There are other disadvantages to using a comma-separated list besides indexing, which I wrote about in my answer to this old post: Is storing a delimited list in a database column really that bad?

I would rather use a proper solution over a frigged solution.

Then store one id per row. In a relational database, that's the proper solution.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271241

You should not be storing lists of numbers in a comma-delimited string. This is a really bad idea:

  • Number should be stored as numbers, not strings.
  • Your numbers appear to be ids. Ids should have explicit foreign keys defined.
  • SQL -- in general -- has lousy string handling functions.
  • SQL cannot optimize the queries with string operations.
  • SQL has a great way of storing lists. It is called a table.

Sometimes, though, we are stuck with other peoples really, really, really, really bad decisions on designing databases. MySQL has a convenient function for this situation:

where find_in_set(1, ShareWithID) > 0

If you have spaces in the string, you need to remove them:

where find_in_set(1, replace(ShareWithID, ' ', '')) > 0

Upvotes: 2

Alan N
Alan N

Reputation: 191

The solution to this problem is to use Gordon Linoff's suggestion of the FIND_IN_SET command in conjunction with the correct configuration of the table column in question, like this:

SELECT * FROM tablename WHERE FIND_IN_SET('1', ShareWithID);

However, because the FIND_IN_SET command allows you to find the position of a string within a comma-separated list of strings, you MUST ensure that the contents of the column contains a comma after each item and DOES NOT contain spaces after the comma.

So this column content used in conjunction with the above command will return '0' rows: 111, 1

While this column content will return '1' row: 111,1

As will this one: 33,1

And this one: 44,1,415

Upvotes: 0

Related Questions