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' ```

Accepted Answer

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.

Wildcard to Match One Or Zero Characters

Solution

Problem