Page 1 of 1
MySQL - count/length
Posted: Fri May 21, 2010 6:36 am
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.
Re: MySQL - count/length
Posted: Fri May 21, 2010 7:00 am
by Benjamin
No, this is not possible in the query itself.
Re: MySQL - count/length
Posted: Fri May 21, 2010 8:16 am
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
Re: MySQL - count/length
Posted: Fri May 21, 2010 8:34 am
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.
Re: MySQL - count/length
Posted: Fri May 21, 2010 10:42 am
by mikosiko
you can try also
Code: Select all
SELECT *,
(length(userids) - length(REPLACE(userids, '|', ''))) AS NUserIds
FROM your-table
ORDER BY NUserIds
Re: MySQL - count/length
Posted: Fri May 21, 2010 3:25 pm
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.
Re: MySQL - count/length
Posted: Fri May 21, 2010 3:46 pm
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
Re: MySQL - count/length
Posted: Fri May 21, 2010 3:52 pm
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
Re: MySQL - count/length
Posted: Fri May 21, 2010 4:05 pm
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.
Re: MySQL - count/length
Posted: Fri May 21, 2010 4:36 pm
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.
Re: MySQL - count/length
Posted: Fri May 21, 2010 7:09 pm
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
Re: MySQL - count/length
Posted: Fri May 21, 2010 7:13 pm
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.
Re: MySQL - count/length
Posted: Sat May 22, 2010 5:29 am
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.
Re: MySQL - count/length
Posted: Sun May 23, 2010 1:06 am
by Benjamin
That makes sense. Is there a standard workaround for previous versions?
Re: MySQL - count/length
Posted: Sun May 23, 2010 2:28 am
by Eran
You have to repeat the expression in the ORDER BY clause, unfortunately