Sort the Table by the value closest to the parameter passed in stored procedures

Asked

Viewed 311 times

0

how to sort a table according to the approximate value of a parameter passed in a stored previous

Ex:

let’s say that in a table the name field has the following values: Tiago, Iago and Thiago and I pass as parameter in the stored "Thiag" field, it should present me the data in the following order:

  1. Thiago
  2. James
  3. Iago

Does anyone know anything that can help me?

sql stored-procedures order-by

  • This is gonna be heavy, 'cause you’re gonna have to sweep all the records from the table. But I think using a trial that uses distance from Evenshtein is an option. You can put a distance limit not to return all records if you want. Which bank you are using?

  • I use sql server

1 answer

0


A solution for postgres would be the following:

with ldist as (
   select name, 
          levenshtein(name, nameParametro)  as distance
   FROM names
) 
select * 
from ldist
order by distance;

If you are using another database will have to be adapted.

  • I am using sql server

  • The idea is the same, you just have to find an implementation of the algorithm from Levenshtein to sqlserver.

  • Just one more question: in the query you gave me, which name would be the value to be passed?

  • I edited to make it clearer. In reality it doesn’t matter the order you put because the distance between the two string will be the same.

Browser other questions tagged sql stored-procedures order-by

You are not signed in. Login or sign up in order to post.