CODEWITHSUNDEEP
sqlmysql
ojreadmore
ojreadmore

Reputation: 1475

SQL with alphabet - thinking like a dictionary

I want to write a query that will match up a string from one table with the closest alphabetical match of a string fragment within another table.

Given: There are 2 tables, table 1 has a record with string "gumby". Table 2 has letters that start words: "g", "ga", "gam", "go", "ha", "hi".

Problem: Because I don't have a "gu" entry or anything more specific (like "gum"), I want "gumby" to match up with "go", because alphabetically "gumby" is less than "h" and more than "go".

Mechanisms I have tried: 1. A statement with "LIKE g%" will return all fragments starting with "g", I only want one result.
2. A statement with "LIKE "g%" and a GROUP BY. That only returns "g".
3. Adding MAX() to that GROUP BY statement will give me "go", but that's not correct if my word were "galaga" -- the correct spot for that would be "ga".

I would really like to accomplish this alphabetizing words solely in SQL.

Upvotes: 2

Views: 967

Answers (2)

cptScarlet
cptScarlet

Reputation: 6136

What about something like

select word from Table_1 where work like 'g%' order by word asc LIMIT 0,1

I'm not sure if that's the exact synatx for Limit, it's been a while since I used mysql

Upvotes: 0

Eric
Eric

Reputation: 95133

SQL compares with the dictionary in mind. You can use this to your advantage, like so:

select
    max(word)
from
    table2 t2
where
    word < @word

To get the list of all words from table1 and the corresponding word from table2, you'd do this:

select
    t1.word,
    (select max(word) as word from table2 where word <= t1.word) as Dictionary
from
    table1 t1

Upvotes: 5

Related Questions