Compare two String with MySQL

Question

I wan't to compare two strings in a SQL request so I can retrieve the best match, the aim is to propose to an operator the best zip code possible. For example, in France, we have Integer Zip code, so I made an easy request :

SELECT *
FROM myTable
ORDER BY abs(zip_code - 75000)

This request returns first the data closest of Paris.

Unfortunatelly, United Kingdom have zip code like AB421RS, so my request can't do it. I see in SQL Server a function 'Difference' : http://www.java2s.com/Code/SQLServer/String-Functions/DIFFERENCEworkoutwhenonestringsoundssimilartoanotherstring.htm

But I use MySQL..

Is there anyone who have a good idea to do the trick in one simple request ?

PS : the Levenshtein Distance will not do it, as I really wan't to compare string like if they were number. ABCDEF have to be closer to AWXYZ than to ZBCDEF.

Cyril Gandon · Accepted Answer · 2010-06-18 15:50:22Z

2

Ok, I have to stop asking question and find answer just after !!

For the community, here is what I will do :

select *
from myTable
order by abs(ascii(substring(zip_code,1,1)) - ascii(substring('AAAAA',1,1))) asc,
         abs(ascii(substring(zip_code,2,1)) - ascii(substring('AAAAA',2,1))) asc,
         abs(ascii(substring(zip_code,3,1)) - ascii(substring('AAAAA',3,1))) asc,
         abs(ascii(substring(zip_code,4,1)) - ascii(substring('AAAAA',4,1))) asc,
         abs(ascii(substring(zip_code,5,1)) - ascii(substring('AAAAA',5,1))) asc

answered Jun 18, 2010 at 15:50

Cyril Gandon

17.1k15 gold badges84 silver badges124 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Compare two String with MySQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related