Wildcard to Match One Or Zero Characters

Question

Is there a short hand for this?
Is there a wildcard that matches on any or null?
Another was to say it is match on zero or one.

select [id], [word] from [FTSwordDef]
 where [word] like 'system_'
    or [word] like 'system'

Answer 1

Yes, there is a shorter way, but it would likely make your query non-sargable (unless it is already so):

WHERE word + ' ' LIKE 'system_'

It works because any extra space on the left side of LIKE is disregarded, while, if it is not extra i.e. if it is within the length of the right side's argument, it takes part in matching the pattern string like any other character.

So, for instance, all of the following would result in true:

(1) 'system ' LIKE 'system_'
(2) 'systemA' LIKE 'system_'
(3) 'systemA ' LIKE 'system_'

In (1), the space matches the _ of the pattern string. In (2), it is A that matches the _. In (3), it is also A while the space is disregarded.

Here's a nice little demo to illustrate this: http://sqlfiddle.com/#!3/d41d8/9521.

Answer 2

Horribly inefficient and I'd prefer Andriy's answer, but you could use wildcard and also compare the length.

select [id], [word] from [FTSwordDef]
  where [word] like 'system%'
    and LEN([word]) <= LEN('system%')