Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

...


PGOracleMySQL
说明

pg_trgm提供了距离操作符( <-> ),用来计算两个字符之间的距离。计算结果范围为0-1,值越小表示两个字符越相似。


utl_match


Code Block
languagebash
themeDJango
linenumberstrue
dnf install postgresql13-contrib -y


Code Block
languagesql
themeDJango
linenumberstrue
postgres=# create extension pg_trgm;
CREATE EXTENSION





查询


Code Block
languagesql
themeDJango
linenumberstrue
postgres=# select * from t_location 
order by name <-> 'Long' limit 10 ;
   name
----------
 Lang
 Lofer
 Loich
 Lorüns
 Lochau
 Leogang
 Lassing
 Lenzing
 Loretto
 Leonding
(10 rows)




Code Block
languagesql
themeEmacs
linenumberstrue
SQL> select * from (
      select * from t_location 
        order by utl_match.edit_distance_similarity(name,'Long')
      desc )
    where rownum < 10 ;

NAME
--------------------------------------------------
Lang
Leogang
Lend
Lans
Leonding
Lengau
Linz
Wang
Langau
9 rows selected

SQL> select * from (
      select * from t_location
        order by utl_match.jaro_winkler_similarity(name,'Long') 
      desc )
    where rownum < 10 ;

NAME
--------------------------------------------------
Lang
Langau
Lengau
Lohnsburg am Kobernau脽erwald
Leogang
Floing
Losenstein
Lockenhaus
Leonding
9 rows selected



...