MySQL - count/length

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
JKM
Forum Contributor
Posts: 221
Joined: Tue Jun 17, 2008 8:12 pm

MySQL - count/length

Post by JKM »

I have a field that is formatted this way: '1|12|36|1124|5874|' (<userid>|), and I was wondering if possible to ORDER by the number of '<number>|'.

I hope you'll understand - thanks for any help.
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Re: MySQL - count/length

Post by Benjamin »

No, this is not possible in the query itself.
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: MySQL - count/length

Post by Eran »

It's possible but not very efficient. Look into the SUBSTR() function - http://dev.mysql.com/doc/refman/5.0/en/ ... ion_substr
User avatar
Apollo
Forum Regular
Posts: 794
Joined: Wed Apr 30, 2008 2:34 am

Re: MySQL - count/length

Post by Apollo »

Obviously the most retarded approach ever, but this does the trick:

Code: Select all

SELECT * , (LENGTH(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(userids,'0',''),'1',''),'2',''),'3',''),'4',''),'5',''),'6',''),'7',''),'8',''),'9',''))+1) AS numberOfUserIds FROM table ORDER BY numberOfUserIds
Better just keep track of the number of userids in a separate field.
mikosiko
Forum Regular
Posts: 757
Joined: Wed Jan 13, 2010 7:22 pm

Re: MySQL - count/length

Post by mikosiko »

you can try also

Code: Select all

SELECT *,
    (length(userids) - length(REPLACE(userids, '|', '')))  AS NUserIds
FROM your-table
ORDER BY NUserIds
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Re: MySQL - count/length

Post by Benjamin »

If it's the first number those solutions may work, but they are all borderline hackish and one of them is like using concrete for tires;). If it's not the first number you are out of luck. I would look into parsing those out and just placing them into a different table or field. You will of course need to ensure that they remain updated.
mikosiko
Forum Regular
Posts: 757
Joined: Wed Jan 13, 2010 7:22 pm

Re: MySQL - count/length

Post by mikosiko »

Benjamin wrote:If it's the first number those solutions may work, but they are all borderline hackish and one of them is like using concrete for tires;). If it's not the first number you are out of luck. ....
I'm always open to learn from the savvy... so, could you please elaborate/explain your answer/comments ... to me is always good to know/learn new and better techniques
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: MySQL - count/length

Post by Eran »

As I understood it, the OP wants to sort by the number of IDs in the cell (not by the IDs themselves). If that's the case, mikosiko gave a very good solution for the given schema. Of course it would be better to normalize it and use proper relationships to do it
mikosiko
Forum Regular
Posts: 757
Joined: Wed Jan 13, 2010 7:22 pm

Re: MySQL - count/length

Post by mikosiko »

pytrin wrote:As I understood it, the OP wants to sort by the number of IDs in the cell (not by the IDs themselves). If that's the case, mikosiko gave a very good solution for the given schema. Of course it would be better to normalize it and use proper relationships to do it
I understood the same ... and +1 in the normalization suggestion.
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Re: MySQL - count/length

Post by Benjamin »

mikosiko wrote:so, could you please elaborate/explain your answer/comments
Let's say you have a field containing data formatted as follows, where <userid> is the number you want to sort by:

[text]1|12|36|1124|5874|<userid>|323|998|53535[/text]

In order to find this field you would need to know the starting and ending index. You actually could use SUBSTRING_INDEX(), LEFT() and LOCATE() to get the value. Without SUBSTRING_INDEX() it would not be possible. So this would look like:

Code: Select all

SELECT
  *
FROM
  table
ORDER BY
  LEFT(SUBSTRING_INDEX(field_name, '|', -4), LOCATE('|', SUBSTRING_INDEX(field_name, '|', -4)) - 1) ASC
But really, with a setup such as this, this is the first of many problems you will run into and I don't see it as being scalable.

If he is trying to sort by the number of integer values in the string, that is an entirely different problem.

Code: Select all

SELECT *,
    (length(userids) - length(REPLACE(userids, '|', '')))  AS NUserIds
FROM your-table
ORDER BY NUserIds
Is off by one and will fail on some MySQL versions because even if the value was correct it would not be ordered by NUserIds. This would work better:

Code: Select all

SELECT
*
FROM
  table_name
ORDER BY
    (length(field_name) - (length(REPLACE(field_name, '|', '')) - 1))  ASC
But this solution will only work if the string contains nothing but numbers, because otherwise it would be counting character values as well.

You could add a field to the existing table which would store the user_id, or the number of numbers, which would be the best solution.

I said this was not possible because I didn't realize that SUBSTRING_INDEX existed (I actually looked for it, but it was getting late), and I knew that MySQL does not have preg_replace functionality. In any case these solutions are certainly not the best but will work in a bind.
mikosiko
Forum Regular
Posts: 757
Joined: Wed Jan 13, 2010 7:22 pm

Re: MySQL - count/length

Post by mikosiko »

thanks Benjamin... that is definitely a more elaborated answer and arguments... better for everyone.

now: lets forget about your first analysis because clearly it will depend if that is the real objective of the OP in which case is also possible to get results using sql as you discovered

Lets look to the second part:
Benjamin wrote: SELECT *,
    (length(userids) - length(REPLACE(userids, '|', '')))  AS NUserIds
FROM your-table
ORDER BY NUserIds


Is off by one and will fail on some MySQL versions because even if the value was correct it would not be ordered by NUserIds
I don't see how it can be "off by one" when the OP's description/example is showing that each field has a "|" delimiter at the end... including the last one, the last part "even when if the value was correct it would not be ordered" is confusing me ....??

granted that the data could contain more characters than the expected that could produce wrong results.

and we agree that normalization is in order.

again... thanks for elaborate
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Re: MySQL - count/length

Post by Benjamin »

Assuming that implode is being used to create these strings, they will not end in a pipe. In that case it would be off by one. In regards to ORDER BY not working, I have seen queries ordering by an alias work fine on one server, but not another. I have never investigated the exact reason.
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: MySQL - count/length

Post by Eran »

I have seen queries ordering by an alias work fine on one server, but not another. I have never investigated the exact reason.
Older versions of MySQL didn't support this. On MySQL 5 it's safe to use an alias for ordering.
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Re: MySQL - count/length

Post by Benjamin »

That makes sense. Is there a standard workaround for previous versions?
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: MySQL - count/length

Post by Eran »

You have to repeat the expression in the ORDER BY clause, unfortunately
Post Reply