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]

Re: order by difference - possible?

John Harman wrote:
[color=blue]
> SELECT name FROM customers WHERE name LIKE "Fred" ORDER BY
> difference(name ,"Fred");[/color]

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]

Re: order by difference - possible?

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" <spammerdream@y ahoo.com> wrote in message
news:b9NXc.467$ wK3.331@read3.i net.fi...[color=blue]
> John Harman wrote:
>[color=green]
> > SELECT name FROM customers WHERE name LIKE "Fred" ORDER BY
> > difference(name ,"Fred");[/color]
>
> 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?[/color]

[Bill Karwin]

Re: order by difference - possible?

John Harman wrote:[color=blue]
> 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.[/color]

How about this?

SELECT C.name
FROM customers C
WHERE C.name LIKE 'Fred'
ORDER BY
ABS(ORD(UPPER(S UBSTRING(C.name ,1,1)))
- ORD(UPPER(SUBST RING('Fred',1,1 )))),
ABS(ORD(UPPER(S UBSTRING(C.name ,2,1)))
- ORD(UPPER(SUBST RING('Fred',2,1 )))),
ABS(ORD(UPPER(S UBSTRING(C.name ,3,1)))
- ORD(UPPER(SUBST RING('Fred',3,1 )))),
ABS(ORD(UPPER(S UBSTRING(C.name ,4,1)))
- ORD(UPPER(SUBST RING('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]

Re: order by difference - possible?

Aggro wrote:[color=blue]
> John Harman wrote:
>[color=green]
>> SELECT name FROM customers WHERE name LIKE "Fred" ORDER BY
>> difference(name ,"Fred");[/color][/color]

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

--
Edd Benson
ebommi@netscape .net

amoebae leave no fossils