Geek Answers Handbook

Match wildcard or null characters

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

select [id], [word] from [FTSwordDef] where [word] like 'system_' or [word] like 'system'
+6
source share
3 answers

Yes, there is a shorter way, but it will most likely make your request not sargable (if that is already not the case):

 WHERE word + ' ' LIKE 'system_'

This works because any extra space on the left side of LIKE ignored, and if it is not extra, that is, if it is within the length of the argument of the right side, it takes part in matching the pattern string, like any other character.

So, for example, all of the following will result in true :

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

In (1), the space corresponds to line _ template line. In (2), the value of A corresponds to _ . In (3) it is also A , while space is not taken into account.

Here is a small demonstration to illustrate: http://sqlfiddle.com/#!3/d41d8/9521 .

+3
source

Terribly inefficient, and I would rather answer Andriy , but you can use a wildcard as well as compare the length.

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

Nope.

http://msdn.microsoft.com/en-us/library/ms179859.aspx

If the percentage symbol will not work, you will need OR. This is an additional part of the proposal, so yes, it is less clean, but using LIKE first of all introduces inefficiency in any case. Rule of thumb: Avoid LIKE if possible, if performance is a problem.

-3
source

More articles:


All Articles