order by difference - possible?

[John Harman]

Hi,

I'm trying to do a MySQL Query using Mysql 3.23.58 something like that below

SELECT name FROM customers WHERE name LIKE "Fred" ORDER BY
difference(name,"Fred");

The difference piece doesn't seem to be working (Syntax Error returned). Is
this not possible without upgrading the server? or am I doing something
wrong, all I want to do is be able to return the results closest to that
entered first.

Thanks in advance

John

[Aggro]

John Harman wrote:

SELECT name FROM customers WHERE name LIKE "Fred" ORDER BY
difference(name,"Fred");

Try something like:

select difference( 'Fred', 'Fred' );

To see if the function you are trying to use works. If the function
works, there are better changes that the real query with the function
works also. If it doesn't work, then there is no way that the query
would work with it.

I have never seen the difference() function and I was not able to find
it from the MySQL manual, where did you find it?

[John Harman]

Hi,

Good idea, still returns the same syntax error.

Looking elsewhere, I think difference is part of the Spatial functions being
added with 4.1

Any ideas how I could achieve the same effect without it?

Thanks for your help so far.

John
"Aggro" <sp**********@yahoo.com> wrote in message
news:b9***************@read3.inet.fi...

John Harman wrote:

SELECT name FROM customers WHERE name LIKE "Fred" ORDER BY
difference(name,"Fred");

Try something like:

select difference( 'Fred', 'Fred' );

To see if the function you are trying to use works. If the function
works, there are better changes that the real query with the function
works also. If it doesn't work, then there is no way that the query
would work with it.

I have never seen the difference() function and I was not able to find
it from the MySQL manual, where did you find it?

[Bill Karwin]

John Harman wrote:

I'm trying to do a MySQL Query using Mysql 3.23.58 something like that below

SELECT name FROM customers WHERE name LIKE "Fred" ORDER BY
difference(name,"Fred");

The difference piece doesn't seem to be working (Syntax Error returned). Is
this not possible without upgrading the server? or am I doing something
wrong, all I want to do is be able to return the results closest to that
entered first.

How about this?

SELECT C.name
FROM customers C
WHERE C.name LIKE 'Fred'
ORDER BY
ABS(ORD(UPPER(SUBSTRING(C.name,1,1)))
- ORD(UPPER(SUBSTRING('Fred',1,1)))),
ABS(ORD(UPPER(SUBSTRING(C.name,2,1)))
- ORD(UPPER(SUBSTRING('Fred',2,1)))),
ABS(ORD(UPPER(SUBSTRING(C.name,3,1)))
- ORD(UPPER(SUBSTRING('Fred',3,1)))),
ABS(ORD(UPPER(SUBSTRING(C.name,4,1)))
- ORD(UPPER(SUBSTRING('Fred',4,1))));

This is a hack -- it isn't scalable to an arbitrary depth of similarity
between strings, but after 4 or 5 characters, it might be adequate to
sort your dataset accurately.

Regards,
Bill K.

[Edd Benson]

Aggro wrote:

John Harman wrote:

SELECT name FROM customers WHERE name LIKE "Fred" ORDER BY
difference(name,"Fred");

SELECT name FROM customers WHERE name LIKE '%Fred%'
^^^^^^^^

--
Edd Benson
eb****@netscape.net

amoebae leave no fossils