How to fetch entries starting with the given string from a SQL Server database?

c# sql sql-server tsql string-comparison
Erlend D. picture Erlend D. · Jul 27, 2010 · Viewed 45.1k times · Source

I have a database with a lot of words to be used in a tag system. I have created the necessary code for an autocomplete box, but I am not sure of how to fetch the matching entries from the database in the most efficient way.

I know of the LIKE command, but it seems to me that it is more of an EQUAL command. I get only the words that looks exactly like the word I enter.

My plan is to read every row, and then use C#'s string.StartsWith() and string.Contains() functions to find words that may fit, but I am thinking that with a large database, it may be inefficient to read every row and then filter them.

Is there a way to read only rows that starts with or contains a given string from SQL Server?

Answer

Fosco picture Fosco · Jul 27, 2010

When using like, you provide a % sign as a wildcard. If you want strings that start with Hello, you would use LIKE 'Hello%' If you wanted strings with Hello anywhere in the string, you would use LIKE '%Hello%'

As for efficiency, using Like is not optimal. You should look into full text search.

Related questions

What represents a double in sql server?

I have a couple of properties in C# which are double and I want to store these in a table in SQL Server, but noticed there is no double type, so what is best to use, decimal or float? This …

Read More
Generate class from database table

How can I generate a class from a SQL Server table object? I'm not talking about using some ORM. I just need to create the entities (simple class). Something like: public class Person { public string Name { get;set; } public string …

Read More
SQL error: Incorrect syntax near the keyword 'User'

I am using SQL to insert data to SQL Database file using C# as follows. String cs = System.Configuration.ConfigurationManager.ConnectionStrings["connection1"].ConnectionString; SqlConnection conn = new SqlConnection(cs); String sql = "INSERT INTO User (login, password, status) " + "VALUES (@login, @password, @status)"; …

Read More