SQL Prompt suggestions order

jonathan.lee

Is there a way to sort the SQL Prompt suggestions by closest match instead of in alphabetical order? (shortest to longest match would be the next best sort order).

(I had a longer post I tried to preview and lost it when prompted to re-login. Let me know if you want me to clarify what I mean by closest match. I have a screenshot if you have a place I can upload/send it to in an email).

Jonathan Lee

David Priddle

Hi Jonathan,

There is currently no way to do this in Prompt. Currently, our order is prefix match, then camel case matching, then checking if what is typed is anywhere else in the name of the suggestion.

If you could clarify closest match, that would be great. Do you mean number of characters that match?

If you have the time, please do add this to our feature suggestion site. We use this to gauge interest in various features and prioritise development of them.

We also have another suggestion to rank suggestions by frequency of use. Would that help in your situation?

Best regards,

David

jonathan.lee

What I mean by closest match varies between match types. In the case for camel case (or underscore-separated first letter matching), the closest match would be the one that has the exact initials. Next closest would be ones with additional suffixes, those with prefixes, and then those with intervening content between matched initials. Somewhere in there should be any matches with the search value as a direct substring. For the case of substring matches, an exact match should be first, then matches closest to the beginning of the name should be listed next, sorted by suffix length (or alphabetical for the same match starting position). Matches further from the beginning of the string would then be listed, sorted as above, etc.

Here are a few example based looking up a table name with the initials of "tcd" (while typing the FROM clause of a SELECT statement):
Note: I've added square brackets around each matching, highlighted letter per the screenshot of the SQL Prompt suggestions.

1. Closest match (the actual table I was searching for): [t]bl[C]oil_[D]ata
2. Suffix to the match: [t]bl[C]oil_[D]ata_New
3. Prefix to the match: Original_[t]bl[C]oil_[D]ata
4. Match with intervening content: [t]bl[C]oil_Width_[D]ata.
5. Mix of prefix and intervening content:
   1. Original_[t]bl[C]oil_Temperature_[D]ata
   2. Original_[t]bl[C]oil_Thickness_[D]ata
   3. Original_[t]blIMS_Exit_LP_[C]entreline_[D]ev
6. Content as a direct substring: Temp[T][C][D]

Another example, for a substring match, "stand" with no exact match (if one exists, it should be listed first, of course):

1. tbl[Stand]s (shortest match)
2. tbl[Stand]_Controllers
3. tbl[Stand]_Controllers_AGC_Data
4. tbl[Stand]_Controllers_CVC_Data <-- # 2 and 3 should be listed in alphabetical order (#1 would fall in the proper place that way too, so it might not need special handling)
5. Original_tbl[Stand]_Controllers_AGC_Data <-- These next ones should be listed after 1-3 instead of before them, as happens currently.
6. Original_tbl[Stand]_Controllers_HGC_Data

Within each type of match, the content could be sorted alphabetically for matches with prefixes and suffixes and from shortest to longest for intervening content (could be number of words instead of total number of characters, one long word like "_temperature_" vs. two short words "_exit_lp_").

With the few (abbreviated) examples I gave, you might think this is much ado about nothing but when there are 20-40 match suggestions and the one you want is near the bottom of the list but should be been an exact match, it is a real pain.

I hope this explanation is clearer. I will add this as a suggestion for new features.

Thanks,
Jonathan

David Priddle

Hi Jonathan,

Thank you for clarifying this and posting it on the UserVoice forum! I'll be taking a good look at this on Monday and see if we can optimise how we show suggestions.

Best regards,

David