SQL - search by word start

Question

I want to write a SQL SERVER statement that looks for the beginning of a word in a line that starts with something.

For example, if I search for "em" in a company entry, I should get:

Emily, Inc.

Emmy

NOT

Forget about him

Lemming, LLC

I can do this in PHP by extracting / cutting a string into an array and looking at the beginning of each word. But how will I write this query on the SQL server without resorting to stored procedures / functions?

Answer 1

JW's answer will work for records where Em is at the very beginning of the field.

If you also need to get values ​​in which the word is not the first in the line, try:

SELECT * FROM tableName WHERE CompanyName LIKE 'Em%' or CompanyName LIKE '% Em%' 

(It is assumed that all words are separated by a space.)

Answer 2

use LIKE

 SELECT * FROM tableName WHERE CompanyName LIKE 'Em%' 

Answer 3

Another variant of CONTAINS , something like:

 SELECT ... WHERE CONTAINS(CompanyName, 'Em*');