can i use mysql functions to sort an alpha-numeric description alphabetically?
Hi, I have looked at the online documentation and the mysql books that I have but can find no answer. I have a field that looks like this: (8+2) Landscape I want to sort on the alphabetic characters. My mysql query does an order by this field and that returns a numeric order (which for me is not terribly useful.) So - is there a function or way I can sort this array alphabetically rather than numerically? Thanks, Nicole -- Nicole Lallande [EMAIL PROTECTED] 760.753.6766 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail mysql-unsubscribe-##L=##[EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: can i use mysql functions to sort an alpha-numeric description alphabetically?
Hi Nicole, Perhaps it's me, but I'm having trouble following you - as I did with the question I've just finished responding to... I have looked at the online documentation and the mysql books that I have but can find no answer. I have a field that looks like this: (8+2) Landscape I take it that this is the data value stored in the field. What does the schema look like? I want to sort on the alphabetic characters. My mysql query does an order by this field and that returns a numeric order (which for me is not terribly useful.) What do you mean by numeric order? If there was another row containing: (72+18) Landscape Then it would likely appear before the row you mentioned - and not in a numeric sequence at all. Do you mean that you want the parentheses, the digits, the plus sign, AND the space character ignored for the purposes od the sequence, and thereafter that the values should be treated as alpha? Is there any possibility of numerics appearing after the first alpha character? Would that matter or would they have to be ignored as well? So - is there a function or way I can sort this array alphabetically rather than numerically? Yes there are various ways and means, right up to the 'expensive' regular expression function. I think we can solve this one quickly enough - given a good understanding so we don't end up chasing red herrings! Please post the query you are using currently, together with a few rows of output. Then highlight the problem by showing the sequence that you would prefer to see. Please advise, =dn - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: can i use mysql functions to sort an alpha-numeric description alphabetically?
Sorry Dan, I had actually replied to Steve who had asked the same question but I forgot to reply to the list with this: here is the query: select catval,catdescr from embiteccat where catzid=$zid and catlid=$lid and catunder=$cat order by catdescr; Here is a some data: 36,1,1,,0,0,2(12+1) Long,,,0,0,prodsku,,6,:6:36:,0,0,1,10 38,1,1,,0,0,24+1 Landscape,,,0,0,prodsku,,6,:6:38:,0,0,1,10 41,1,1,,0,0,6 Portrait,,,0,0,prodsku,,6,:6:41:,0,0,1,10 43,1,1,,0,0,24+1 Long,,,0,0,prodsku,,6,:6:43:,0,0,1,10 44,1,1,,0,0,2(24+1) X-Long,,,0,0,prodsku,,6,:6:44:,0,0,1,10 where catdesc is the 7th field (ie, 2(12+1) Long etc. So far all I can think of is that I have to load it into an array, sort the array with some php function or grep and then spit it out... So - yes you are correct - I want everything before the alphabetic characters, (number, parenthesis, + sign) to be ignored and then to sort by the alpha. TIA, Nicole DL Neil wrote: Hi Nicole, Perhaps it's me, but I'm having trouble following you - as I did with the question I've just finished responding to... I have looked at the online documentation and the mysql books that I have but can find no answer. I have a field that looks like this: (8+2) Landscape I take it that this is the data value stored in the field. What does the schema look like? I want to sort on the alphabetic characters. My mysql query does an order by this field and that returns a numeric order (which for me is not terribly useful.) What do you mean by numeric order? If there was another row containing: (72+18) Landscape Then it would likely appear before the row you mentioned - and not in a numeric sequence at all. Do you mean that you want the parentheses, the digits, the plus sign, AND the space character ignored for the purposes od the sequence, and thereafter that the values should be treated as alpha? Is there any possibility of numerics appearing after the first alpha character? Would that matter or would they have to be ignored as well? So - is there a function or way I can sort this array alphabetically rather than numerically? Yes there are various ways and means, right up to the 'expensive' regular expression function. I think we can solve this one quickly enough - given a good understanding so we don't end up chasing red herrings! Please post the query you are using currently, together with a few rows of output. Then highlight the problem by showing the sequence that you would prefer to see. Please advise, =dn - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php -- Nicole Lallande [EMAIL PROTECTED] 760.753.6766 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail mysql-unsubscribe-##L=##[EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: can i use mysql functions to sort an alpha-numeric description alphabetically?
Nicole, RTFM: 6.3 Functions for Use in SELECT and WHERE Clauses in particular the String Functions If the first space (in every row) can be taken as the 'marker' of what to remove (including the space itself), then take a look at the following: SELECT catval, RIGHT( strcatdescr, POSITION( ' ' IN strcatdescr ) + 1 ) AS cd FROM embiteccat WHERE catzid=$zid AND catlid=$lid AND catunder=$cat ORDER BY cd; If however there may be more than one space prior to the commencement of the alpha data, then can we search for the last space (ie the first from the right-hand end of the field)? In which case substitute: SUBSTRING_INDEX( strcatdescr, ' ', -1 ) AS cd It's kind of fun to play games like this, but it isn't very 'relational'. Both of the assumptions (above) are not 'good form'. Even if one is acceptable to you, and of course 'in spades' if neither is, should be the consideration of adding another column to the table for sequencing purposes. How are we doing? =dn (David) Sorry Dan, I had actually replied to Steve who had asked the same question but I forgot to reply to the list with this: here is the query: select catval,catdescr from embiteccat where catzid=$zid and catlid=$lid and catunder=$cat order by catdescr; Here is a some data: 36,1,1,,0,0,2(12+1) Long,,,0,0,prodsku,,6,:6:36:,0,0,1,10 38,1,1,,0,0,24+1 Landscape,,,0,0,prodsku,,6,:6:38:,0,0,1,10 41,1,1,,0,0,6 Portrait,,,0,0,prodsku,,6,:6:41:,0,0,1,10 43,1,1,,0,0,24+1 Long,,,0,0,prodsku,,6,:6:43:,0,0,1,10 44,1,1,,0,0,2(24+1) X-Long,,,0,0,prodsku,,6,:6:44:,0,0,1,10 where catdesc is the 7th field (ie, 2(12+1) Long etc. So far all I can think of is that I have to load it into an array, sort the array with some php function or grep and then spit it out... So - yes you are correct - I want everything before the alphabetic characters, (number, parenthesis, + sign) to be ignored and then to sort by the alpha. TIA, Nicole DL Neil wrote: Hi Nicole, Perhaps it's me, but I'm having trouble following you - as I did with the question I've just finished responding to... I have looked at the online documentation and the mysql books that I have but can find no answer. I have a field that looks like this: (8+2) Landscape I take it that this is the data value stored in the field. What does the schema look like? I want to sort on the alphabetic characters. My mysql query does an order by this field and that returns a numeric order (which for me is not terribly useful.) What do you mean by numeric order? If there was another row containing: (72+18) Landscape Then it would likely appear before the row you mentioned - and not in a numeric sequence at all. Do you mean that you want the parentheses, the digits, the plus sign, AND the space character ignored for the purposes od the sequence, and thereafter that the values should be treated as alpha? Is there any possibility of numerics appearing after the first alpha character? Would that matter or would they have to be ignored as well? So - is there a function or way I can sort this array alphabetically rather than numerically? Yes there are various ways and means, right up to the 'expensive' regular expression function. I think we can solve this one quickly enough - given a good understanding so we don't end up chasing red herrings! Please post the query you are using currently, together with a few rows of output. Then highlight the problem by showing the sequence that you would prefer to see. Please advise, =dn - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php -- Nicole Lallande [EMAIL PROTECTED] 760.753.6766 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail mysql-unsubscribe-##L=##[EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
