Scott,
I really don't understand your need to convert your string values to some
kind of numeric equivalent. When it comes to sorting strings it all
depends on the collation sequence for the characterset you are using.
For example, my STATUS; command tells me:
mysql Ver 14.3 Distrib 4.1.1a-alpha, for Win95/Win98 (i32)
Connection id: 1
Current database: test
Current user: [EMAIL PROTECTED]
SSL: Not in use
Using delimiter: ;
Server version: 4.1.1a-alpha-nt-log
Protocol version: 10
Connection: localhost via TCP/IP
Client characterset: latin1_swedish_ci
Server characterset: latin1_swedish_ci
TCP port: 3306
Uptime: 2 hours 59 min 30 sec
Threads: 1 Questions: 19 Slow queries: 0 Opens: 12 Flush tables: 1
Open tables: 5 Queries per second avg: 0.002
(This is a development machine that has seen very little work yet today)
My "Server charset" is "latin1_swedish_ci". From the source code for
MySQL, the ASCII collation order (sorting sequence) for that character set
is:
static uchar sort_order_latin1[] = {
0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15,
16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31,
32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47,
48, 49, 50, 51, 52, 53, 54, 55, 56, 57, 58, 59, 60, 61, 62, 63,
64, 65, 66, 67, 68, 69, 70, 71, 72, 73, 74, 75, 76, 77, 78, 79,
80, 81, 82, 83, 84, 85, 86, 87, 88, 89, 90, 91, 92, 93, 94, 95,
96, 65, 66, 67, 68, 69, 70, 71, 72, 73, 74, 75, 76, 77, 78, 79,
80, 81, 82, 83, 84, 85, 86, 87, 88, 89, 90,123,124,125,126,127,
128,129,130,131,132,133,134,135,136,137,138,139,140,141,142,143,
144,145,146,147,148,149,150,151,152,153,154,155,156,157,158,159,
160,161,162,163,164,165,166,167,168,169,170,171,172,173,174,175,
176,177,178,179,180,181,182,183,184,185,186,187,188,189,190,191,
65, 65, 65, 65, 92, 91, 92, 67, 69, 69, 69, 69, 73, 73, 73, 73,
68, 78, 79, 79, 79, 79, 93,215,216, 85, 85, 85, 89, 89,222,223,
65, 65, 65, 65, 92, 91, 92, 67, 69, 69, 69, 69, 73, 73, 73, 73,
68, 78, 79, 79, 79, 79, 93,247,216, 85, 85, 85, 89, 89,222,255
};
You can see that the characters that represent the numbers "0" through "9"
(positions 48 through 57) are sorted in order and before the capital
letters "A" through "Z" (65 through 90). The lower-case letters "a"
through "z" are assigned the same sort value as their capital counterparts
(positions 97 through 122). This means that "ADAM" and "adam" will sort
together (the collation order is case-insensitive, thus the "ci" at the
end of the name)
That also means that "00 apple" will sort before "01 apple" and that "1
cherry" and "10 cherry" will sort before "2 cherry" ("1" comes
*alphabetically* before "2"). This is because the numbers do not exist as
separate entities within a string. To the CPU, all of the elements of a
string are viewed as numeric values (their ASCII or UNICODE values) that
get sorted according to the rules built into the characterset being used
(see table above).
So, please try to explain again why an ORDER BY fieldname (without any
sort of conversion function on your field) does not work as you want it
to.
Thank you for your patience with me,
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
"Scott Fletcher" <[EMAIL PROTECTED]> wrote on 07/28/2004 10:42:39 AM:
> Okay, redo(ed) the scripts and eliminated all of the CHAR() part
> from all of the SQL Syntax in the PHP scripts and it look better.
> What functions can I use to substitute the INTEGER() and DOUBLE()
> from DB2 to MySQL?. I?ll keep searching the MySQL but I don?t seem
> to find a MySQL function that would do something similar to this.
> Here I got by the ?ORDER BY field_name ASC? for the integer value?
>
> --snip?
> 10
> 11
> 129
> 21
> 23
> --snip?
>
> Where 129 should be at the bottom in integer value. Similar concept
> with the double or float value?
>
> Thanks,
> FletchSOD
>
>
> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
> Sent: Wednesday, July 28, 2004 8:56 AM
> To: Scott Fletcher
> Cc: [EMAIL PROTECTED]
> Subject: RE: Using function followed by "order by
> function_name(table.field_name)".....
>
>
> Let me quote a little bit from the docs (http://dev.mysql.
> com/doc/mysql/en/String_functions.html):
>
> CHAR() interprets the arguments as integers and returns a string
> consisting of the characters given by the code values of those
> integers. NULL values are skipped.
>
> mysql> SELECT CHAR(77,121,83,81,'76');
> -> 'MySQL'
> mysql> SELECT CHAR(77,77.3,'77.3');
> -> 'MMM'
>
> That means that when it processes each of the fields in your
> resultset, it converts that string into some other string and is
> sorting by the results of that transformation. Definitely NOT what
> you wanted. Please, let's try again. Please, show me the results of
> just a plain "ORDER BY field_name" query (without the CHAR()
> function) and explain to me what is wrong...
>
> Yours,
> Shawn Green
> Database Administrator
> Unimin Corporation - Spruce Pine
>
> "Scott Fletcher" <[EMAIL PROTECTED]> wrote on 07/28/2004 09:12:45 AM:
>
> > The example of ORDER BY that didn?t solve the problem is ?
> >
> > --snip--
> > Debug Test (WM Account) 1
> > ABC Company
> > Riverknoll A C
> > SDFONE'sdf
> > SeaWater
> > --snip--
> >
> > which should be ?
> >
> > --snip--
> > ABC Company
> > Debug Test (WM Account) 1
> > Riverknoll A C
> > SDFONE'sdf
> > SeaWater
> > --snip--
> >
> > The word, ?Debug Test (WM Account) 1? came before the ?ABC Company?
> > which should be the other way around?. This show that the ?ORDER BY
> > CHAR(??)? doesn?t work. I just don?t see how to use a function that
> > would sort the char in ascending order properly?
> >
> > Thanks,
> > FletchSOD
> >
> >
> >
> > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
> > Sent: Wednesday, July 28, 2004 7:58 AM
> > To: Scott Fletcher
> > Cc: [EMAIL PROTECTED]
> > Subject: Re: Using function followed by "order by
> > function_name(table.field_name)".....
> >
> >
> > I hope you have already tried just plain-old "ORDER BY field_name"
> > in your query. What is wrong with the default order presented? Can
> > you show an example of how the ORDER BY did not solve your problem
> > along with an example of what it should have been for your situation?
> >
> > Thanks in advance,
> > Shawn Green
> > Database Administrator
> > Unimin Corporation - Spruce Pine
> >
> >
> > "Scott Fletcher" <[EMAIL PROTECTED]> wrote on 07/27/2004 05:29:05
PM:
> >
> > > I'm having a little bit of a trouble with the use of the SQL
function...
> > > What I have here is a webpage that show the row of data, the web
user
> > > get to click on the field to sort the row by the field name. So,
when I
> > > use "order by char(field_name)", the data doesn't turned out right
in
> > > alpha-numeric in ascending order...
> > >
> > >
> > >
> > > --snip--
> > >
> > > Debug Test (WM Account) 1
> > >
> > > ABC Company
> > >
> > > Riverknoll A C
> > >
> > > SDFONE'sdf
> > >
> > > SeaWater
> > >
> > > --snip--
> > >
> > >
> > >
> > > I would like the first few letter of each row be in correct
> > > alpha-numeric order regardless of what character is there in the
data,
> > > like (, ), ', ?, etc...
> > >
> > >
> > >
> > > Thanks,
> > >
> > > FletchSOD
> > >
