Hello. Please help with the request. There is a table with data:

ID | CODE 1 |1 2 |112 3 |1113 4 |11114

How to build a query so that I could choose a record with the maximum number of matches at the beginning of the line.

Suppose that in the sample condition we transfer 11114980349, the condition is most consistent with the record 11114. DBMS PostgreSQL. Is it possible to build a query without full-text search?

    2 answers 2

    In principle, you can of course, but whether you need it ... At least, such queries will definitely not be able to use indexes and will work by exhaustively scanning the table. Perhaps it would be worthwhile to look for other ways ...

     select code from Table order by length(array_to_string(regexp_matches(code, '^11?1?1?4?9?8?0?3?4?9?'),'')) desc limit 1;

    ? have to use regular expressions and arrange characters ? after each character expression, starting with the second. regexp_matches returns an array of all matches, in our case it can be only one, since we have limited ourselves to the beginning of the line (put ^ ), and the maximum matched part of the line will be returned. It remains to sort by the number of matched characters in descending order and take one line.

      So?

       select max(code) from T where '11114980349' like code || '%'
      • The author of the question also cited examples of the type 1113, I understand that they are considered appropriate, but less so. those. after the required piece of string there may be something else. And then your like won't work ... - Mike
      • Mike, it will always work my Like. Poems. :-) - msi
      • It seemed to me according to the condition of the problem that 1111400 is also a suitable candidate. And '11114980349' like '1111400%' will not work - Mike
      • Mike, you understood the problem like this, and I did otherwise. Without an author, there is nothing to argue about. - msi