CODEWITHSUNDEEP
sqldjangodjango-models
aref mousavi
aref mousavi

Reputation: 9

sql query or django filter for string

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

Upvotes: 1

Views: 90

Answers (2)

aref mousavi
aref mousavi

Reputation: 9

best one i found is this:

SELECT id, 
FROM table        
WHERE '3552' LIKE prefix || '%'        
ORDER  BY prefix DESC        
LIMIT  1

thank you all

Upvotes: -1

Pepe N O
Pepe N O

Reputation: 2344

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

Upvotes: 2

Related Questions