SqLite - find beginning of a given string in the table-CodePudding

I have a String, for example ABC_Michael_A_V1.3_Update

In my SQLite database, i have a table beginnings with 2 columns. The first one is of type String and contains multiple possible beginnings of the string, the second one is of type Integer. for example:

StringBeginning   |  Score
------------------------------
ABC_Michael_C     |   2
ABC_Mich          |   5
ABC_Johannes_A    |   4
ABC_Michael_A     |   5
ABC_Michael_A_V1  |   7       <-----
ABC_Jack_A_V1.2   |   12

What I would need to find, is the row with the string that hast the longest matching beginning with my inputstring. (in this sample marked with a row.) Please note, that also row 2 and 4 match, but they are not the longest one.

I know how to do it the other way around, having the beginning of a string and searching all strings that start with that string, but I dont know how to do it if my input string is longer than what I am searching for.

Any help is greatly appreciated

CodePudding user response：

you can sort results by number of characters found.

Try this

select *,
length(replace('ABC_Michael_A_V1.3_Update',StringBeginning,'')) as len
from beginnings 
where instr('ABC_Michael_A_V1.3_Update',StringBeginning) = 1 
order by len asc

CodePudding user response：

I managed to find a solution:

select *, length(StringBeginning) as len from beginnings where instr('ABC_Michael_A_V1.3_Update',StringBeginning) = 1 order by len desc limit 1