CODEWITHSUNDEEP
sql-serversql-server-2005
Torpedo
Torpedo

Reputation: 139

How to include empty string in a like query in sql server

This seems an easy thing but somehow I'm not able to think of it.

I want to write a like query in this format:

WHERE Searchfield LIKE '[ ;]Mark'

Now this would check for an occurence of

' Mark', ';Mark'

I want it to be able to search the following string as well:

'Mark'

How do I indicate that it can have the occurrence of a space, or a semicolon or no character.

Upvotes: 1

Views: 2210

Answers (2)

jerry
jerry

Reputation: 1857

This looks like the kind of thing fulltext was designed for. If its a common query call, Use fulltext search. Then your search term will simply be 'Mark'.

Upvotes: 0

Martin Smith
Martin Smith

Reputation: 453608

Probably easiest to just use

WHERE Searchfield LIKE '[ ;]Mark' OR  Searchfield = 'Mark'

If Searchfield is indexed then you might find that 

WHERE Searchfield IN ('Mark', ' Mark', ';Mark')

gives you a better plan. There is no equivalent in the Like pattern syntax to a regular expression optional character class. Though you could use CLR regular expressions and the following regular expression [ ;]?Mark

Upvotes: 5

Related Questions