locked
SQL Query RRS feed

  • Question

  • User-829096311 posted

    Hi

    I have two tables: dictionary table that contains word field, 
    and Spell that is an empty table and contain correct and wrong fields
    I want to generate a spelling dictionary for words that start with the letter C 

    +----+-------------+-----------+
    | id | CorrectWord | WrongWord |
    +----+-------------+-----------+
    | 1  | camera      | kamera    |
    +----+-------------+-----------+
    | 2  | car         | kar       |
    +----+-------------+-----------+
    | 3  | card        | kard      |
    +----+-------------+-----------+

    I'm looking for something similar to this code, in T-sql language.

    Thanks in advance


    Sql = "Select word FROM dictionary WHERE word Like ""C%""" 'Words that start with C While rdr.Read() 'for each row CorrectWord = rdr("word") 'e.g. Cat Wrongword = Replace_First_Letter_With_K("word") 'e.g. Kat If RowCount("select * from dictionary WHERE word = " & Wrongword) = 0 Then 'If 'Kat' does not exist in the 'Dictionary' table InsertCommand = "INSERT INTO spell (correct, wrong) VALUES(""{0}"", ""{1}"")", CorrectWord, WrongWord Insert() End If End While

    Monday, July 16, 2018 2:29 AM

Answers

  • User347430248 posted

    Hi ThisIsYusuf ,

    I can see that you are trying to create a dictionary for words and you are trying to storing correct words and wrong words.

    Here, I think that you not need to store wrong words, because combination of wrong words has no any limit and user can input anything.

    Also it is not a good practice to store incorrect words in your dictionary.

    I suggest you to only store the correct words and use 'Soundex' and 'difference' function to find the correct words from your table.

    For Example, I store below data in my table.

    Fire some queries to find correct word from table.

    SELECT word
    FROM speliing_data
    WHERE SOUNDEX('card') like SOUNDEX(word)
    
    
    
    SELECT * FROM speliing_data
    WHERE DIFFERENCE(word,'kamera') >= 3
    
    SELECT * FROM speliing_data
    WHERE DIFFERENCE(word,'kar') >= 3
    
    SELECT * FROM speliing_data
    WHERE DIFFERENCE(word,'kard') >= 3

    Output:

    Reference:

    SOUNDEX (Transact-SQL)

    DIFFERENCE (Transact-SQL)

    You can find more examples online to get detailed idea, Based on that you can again think about your table design to store only correct words.

    Regards

    Deepak

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, July 19, 2018 8:29 AM

All replies

  • User347430248 posted

    Hi ThisIsYusuf ,

    I can see that you are trying to create a dictionary for words and you are trying to storing correct words and wrong words.

    Here, I think that you not need to store wrong words, because combination of wrong words has no any limit and user can input anything.

    Also it is not a good practice to store incorrect words in your dictionary.

    I suggest you to only store the correct words and use 'Soundex' and 'difference' function to find the correct words from your table.

    For Example, I store below data in my table.

    Fire some queries to find correct word from table.

    SELECT word
    FROM speliing_data
    WHERE SOUNDEX('card') like SOUNDEX(word)
    
    
    
    SELECT * FROM speliing_data
    WHERE DIFFERENCE(word,'kamera') >= 3
    
    SELECT * FROM speliing_data
    WHERE DIFFERENCE(word,'kar') >= 3
    
    SELECT * FROM speliing_data
    WHERE DIFFERENCE(word,'kard') >= 3

    Output:

    Reference:

    SOUNDEX (Transact-SQL)

    DIFFERENCE (Transact-SQL)

    You can find more examples online to get detailed idea, Based on that you can again think about your table design to store only correct words.

    Regards

    Deepak

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, July 19, 2018 8:29 AM
  • User-829096311 posted

    Thank you so much for the answer

    Monday, July 23, 2018 3:36 PM