hello i need a query that search in database and return a row that has most similarity from starting character with a value. imagine, given string is 'abcdefghijklmnop' our database table has a column named x and for this column, rows are: 1- 'a' 2- 'abc' 3- 'absde' 4- 'abcdef' 5- 'abcdefg' 6- '1abcdefg'
and it should return the row number 5
In postgres exists a function for similarity, first create the extension that contains it
create extension pg_trgm;
after that select using a percentage of similarity like this (e.g. 80% or your desired value)
select x from "table" where similarity('abcdefghijklmnop', x)>0.35;
or the most similar string would be
select * from "table" order by similarity('abcdefghijklmnop', x) desc limit 1;
Fiddle http://sqlfiddle.com/#!17/c901e/2 and docs https://www.postgresql.org/docs/current/pgtrgm.html