SQL - search by beginning of a word

Question

I want to write an SQL SERVER statement that searches for the beginning of a word in a string that starts with something.

For example, if I search for 'em' on the Company record, I should get:

Emily, Inc

The Emmmy

NOT

Forget Them

Lemming, LLC

I can do this in PHP by extracting/slicing the string into an array and searching the beginning of each words. But how I would write this query in SQL server without resorting to Stored procedures/functions?

Answer 1

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

If you also need to retrieve values where the word is not the first in the string, try:

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

(This is assuming all word are separated by a space.)

Answer 2

use LIKE

SELECT * FROM tableName WHERE CompanyName LIKE 'Em%'