Page 1 of 1

Sort a Numbering System with Dashes in It

Posted: Tue Jul 22, 2008 4:10 pm
by kinggabbo
My company has documents with a numbering system as follows: 1-1, 1-2, 2-3, 5-3, 10-2, 2-101, etc... Numbers and dashes. I am storing info about these documents, such as these numbers in a mysql database and then outputting the info into an HTML table on the web page. I was wanting to be able to sort the table ascending and descending based on this numbering system. Does anyone have any ideas how to do this? I tried converting the dashes to decimals but that fails whenever you have more than two numbers after the dash. Any help would be appreciated.

Re: Sort a Numbering System with Dashes in It

Posted: Tue Jul 22, 2008 4:53 pm
by alex.barylski
MySQL should sort those documents without problem...

Maybe your query is incorrect. POST the SQL query and your table schema.

Re: Sort a Numbering System with Dashes in It

Posted: Tue Jul 22, 2008 5:07 pm
by WebbieDave
Hockey wrote:MySQL should sort those documents without problem
Right. If, for some reason unbeknownst to us, you need to sort these elements without benefit of SQL (ORDER BY), you can use usort.
http://us.php.net/manual/en/function.usort.php

Re: Sort a Numbering System with Dashes in It

Posted: Tue Jul 22, 2008 9:10 pm
by manixrock
try the following (not tested):

Code: Select all

ORDER BY CONVERT(INT, SUBSTRING_INDEX(nr_colomn, '-', 1)), CONVERT(INT, SUBSTRING_INDEX(nr_colomn, '-', -1))

Re: Sort a Numbering System with Dashes in It

Posted: Wed Jul 23, 2008 2:32 pm
by kinggabbo
I would like it work using SQL if possible. The name of my field that needs to be sorted(with the dashes in it) is named Exhibit Number. It is currently a varchar type. I tried this sql command based on the last post. I'm not sure I did the command correctly.

$results=$exhibits->query("select * from exhibits ORDER BY CONVERT(INT, SUBSTRING_INDEX('`Exhibit Number`', '-', 1)), CONVERT(INT, SUBSTRING_INDEX('`Exhibit Number`', '-', -1))");

Re: Sort a Numbering System with Dashes in It

Posted: Wed Jul 23, 2008 2:41 pm
by ghurtado
Hockey wrote:MySQL should sort those documents without problem...
Wouldn't alphabetical sorting on that column do something like this?

1-1
1-10
1-2
1-3

.... etc

In that case, that's probably not what you would want, right?

Re: Sort a Numbering System with Dashes in It

Posted: Wed Jul 23, 2008 2:46 pm
by kinggabbo
I think I just tried a regular ORDER BY and that's what it did. That is not what I'm looking for.

Re: Sort a Numbering System with Dashes in It

Posted: Wed Jul 23, 2008 2:55 pm
by Christopher
Easiest would be to either separate the two numbers into two columns in the table, or zero pad them internally "01-07". Or do something like:

ORDER BY CAST(LEFT(mycode, LOCATE('-', mycode)-1) AS UNSIGNED), CAST(SUBSTRING(mycode, LOCATE('-', mycode)+1) AS UNSIGNED)

Re: Sort a Numbering System with Dashes in It

Posted: Wed Jul 23, 2008 3:32 pm
by ghurtado
I think this problem may be close to impossible to resolve in SQL alone. Your biggest hurdle is the fact that you have to split the document ID by the dashes and then add zero padding to the document number, otherwise documents 1-12 and 11-2 would both wind up as "112".

In PHP, your best bet may be to retrieve all the records from the database, split the document ID string and add zero padding, then do the actual sort.

My advice would be to standardize in a document ID format that is "sortable" for the future; that is, require zero padding when entering document IDs.

Re: Sort a Numbering System with Dashes in It

Posted: Wed Jul 23, 2008 4:01 pm
by kinggabbo
Would this work with documents that might have 3 digits after the dash, such as 1-202? Also, if I was somehow able split the document IDs and and add the zeros, how would I sort the whole table based on the document ID field? Would I use a php sort method? Or use SQL ORDER BY on the split document ID fields?

Re: Sort a Numbering System with Dashes in It

Posted: Wed Jul 23, 2008 4:15 pm
by Christopher
You really need to start trying things. Both manixrock and I have given you SQL. They do essentially the same thing.

Re: Sort a Numbering System with Dashes in It

Posted: Wed Jul 23, 2008 4:58 pm
by manixrock
kinggabbo wrote:I would like it work using SQL if possible. The name of my field that needs to be sorted(with the dashes in it) is named Exhibit Number. It is currently a varchar type. I tried this sql command based on the last post. I'm not sure I did the command correctly.

$results=$exhibits->query("select * from exhibits ORDER BY CONVERT(INT, SUBSTRING_INDEX('`Exhibit Number`', '-', 1)), CONVERT(INT, SUBSTRING_INDEX('`Exhibit Number`', '-', -1))");

You need to pass as the first parameter to SUBSTRING_INDEX the name of the column (without any dashes or quotes) holding the dash-separated numbers. For example, if the column name is "product_ids", then your query should be:

Code: Select all

ORDER BY CONVERT(INT, SUBSTRING_INDEX(product_ids, '-', 1)), CONVERT(INT, SUBSTRING_INDEX(product_ids, '-', -1))

Re: Sort a Numbering System with Dashes in It

Posted: Wed Jul 23, 2008 7:01 pm
by kinggabbo
I found help on another forum that was similar to what some had posted here. I still need to test it some, but I think it works. Here is the SQL:

SELECT * FROM table ORDER BY
CAST(LEFT(`Exhibit Number`, LOCATE('-', `Exhibit Number`)-1) AS SIGNED INTEGER),
CAST(RIGHT(`Exhibit Number`, LENGTH(`Exhibit Number`) - LOCATE('-', `Exhibit Number`)) AS SIGNED INTEGER)


Thanks for all the help!

Re: Sort a Numbering System with Dashes in It

Posted: Thu Jul 24, 2008 12:09 pm
by ghurtado
Works like a charm indeed

When I said "I think this problem may be close to impossible to resolve in SQL alone." I did not realize that you only ever have one dash in your document ID, I misunderstood and thought your problem was that you could have an arbitrarily long doc ID like 1-23-1234-123-45...

Glad you found the answer, and thanks for sharing it!