MySQL - count/length
Moderator: General Moderators
MySQL - count/length
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.
I hope you'll understand - thanks for any help.
Re: MySQL - count/length
No, this is not possible in the query itself.
Re: MySQL - count/length
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
Obviously the most retarded approach ever, but this does the trick:
Better just keep track of the number of userids in a separate field.
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 numberOfUserIdsRe: MySQL - count/length
you can try also
Code: Select all
SELECT *,
(length(userids) - length(REPLACE(userids, '|', ''))) AS NUserIds
FROM your-table
ORDER BY NUserIdsRe: MySQL - count/length
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
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 techniquesBenjamin 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. ....
Re: MySQL - count/length
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
I understood the same ... and +1 in the normalization suggestion.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
Re: MySQL - count/length
Let's say you have a field containing data formatted as follows, where <userid> is the number you want to sort by:mikosiko wrote:so, could you please elaborate/explain your answer/comments
[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
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 NUserIdsCode: Select all
SELECT
*
FROM
table_name
ORDER BY
(length(field_name) - (length(REPLACE(field_name, '|', '')) - 1)) ASC
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
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:
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
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:
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 ....??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
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
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
Older versions of MySQL didn't support this. On MySQL 5 it's safe to use an alias for ordering.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
That makes sense. Is there a standard workaround for previous versions?
Re: MySQL - count/length
You have to repeat the expression in the ORDER BY clause, unfortunately