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

Question

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?

Solution 1

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.

Solution 2

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.

That's because you aren't using wildcards:

WHERE column LIKE 'abc%'

...will return rows where the column value starts with "abc". I'll point out that when using wildcards, this is the only version that can make use of an index on the column... er column.

WHERE column LIKE '%abc%'

...will return rows where the column value contains "abc" anywhere in it. Wildcarding the left side of a LIKE guarantees that an index can not be used.

SQL Server doesn't natively support regular expressions - you have to use CLR functions to gain access to the functionality. But it performs on par with LIKE.

Full Text Search (FTS) is the best means of searching text.

Solution 3

You can also implement a StartWith functionality using the following statements:

LEFT('String in wich you search', X) = 'abc'

CHARINDEX('abc', 'String in wich you search') = 1

'String in wich you search' LIKE 'abc%'

Use the one wich performs best.

Solution 4

You can use CONTAINS in T-SQL, but I'm pretty sure you have to have to be using full-text indexing for the table involved in your query.

Contains

Getting started with Full-Text Search

View more solutions

Comments

[Erlend D.]

I actually knew about the % sign. Silly of me. I'll look into full text search too. Thanks!

[Admin]

If what you search for is the underscore character, you need to escape it: WHERE column LIKE '/_%' ESCAPE '/'