There isn't a magic option to find the "correct" spelling of the user's search term. And even if there was, there's no guarantee that the data in the database would be spelled "correctly" anyway.
The closest you can get is to calculate the difference between the
soundex[
^] codes for the database value and the search term. SQL Server has a built-in function to do that, which returns a value from
0
to
4
, where
0
means not at all similar, and
4
means virtually identical.
SqlDataAdapter sqlDa = new SqlDataAdapter("SELECT * FROM stock WHERE DIFFERENCE(pname, @pname) > 1", con);
sqlDa.SelectCommand.Parameters.AddWithValue("@pname", textBox2.Text);
DIFFERENCE (Transact-SQL) - SQL Server | Microsoft Docs[
^]
NB: Soundex is specifically based on English pronunciation. Depending on your users, your results may vary.
There are other methods for comparing similar-sounding strings, but none that are built in to SQL Server. For example, PostgreSQL offers:
PostgreSQL: Documentation: 9.1: fuzzystrmatch[
^]