by Michael S. Kaplan, published on 2006/11/27 10:56 -05:00, original URI: http://blogs.msdn.com/b/michkap/archive/2006/11/27/1160195.aspx
Attention all database developers out there!
I have been somewhat dismayed at getting so few comments after I posted Wild[card] thing, You make my CHAR sing.
Is this really what people expect out of wildcards? Is there really so little interest in seeing the issue explained and documented? And maybe even future versions of query processors having the syntax expanded to support the notion that the engine underneath it does (in regards to "single characters that are actually single sort elements")?
It also affects Access (all versions) and Exchange/ESENT, yet it appears not to be documented anywhere....
Let me know what you think, our phone lines are open. :-)
This post brought to you by ࿄ (U+0fc4, a.k.a. TIBETAN SYMBOL DRIL BU)
Martin Ritchie on 27 Nov 2006 11:28 AM:
I think the reason why so few comments is that we database developers are so used to the lousy string and pattern functions in SQL server. The string functions are terrible (REPLACE, PATINDEX, etc.) and the LIKE operator desperatly needs to be replaced by some sort of Regular Expression tool.
Your post basically said that SQL Server handles strings poorly. Tell us something we don't know. Microsoft always seems to cut improving the string functions from every new version. To be honest, although I am a big fan of SQL Server, when I use other DBMS like MySQL I am envious of thier string tools.
Simon on 27 Nov 2006 12:12 PM:
I don't know about others but for me the LIKE operator has always been an important one when querying databases for information the user is interested in, therefore I knew the syntax for several servers already.
It was annoying (* and ? were the obvious choices initially) when I was learning a long time ago, but it was well documented in a variety of places and the similarity to regular expressions meant I can guess it.
Also, people tend to not comment on good, self explanatory articles (instead preferring to just reference them). They do question articles that have things they don't understand, and they also argue or agree with opinion pieces.
Dean Harding on 27 Nov 2006 5:32 PM:
As I said in the last post, most serious database developers wouldn't be using the LIKE operator like that anyway (if they use it at all). It's quite limiting to begin with (again, since its difficult to use LIKE in an index-friendly way).
Generally you're better off doing any complicated string-matching in client-side code (or SQLCLR if you really want to in the database). Mostly, you'd just use LIKE as in "WHERE X LIKE 'xyz%'" and that's about the only pattern I'd recommend.
bmm6o on 27 Nov 2006 6:47 PM:
Just chiming in to second what has been said already. Column indexes usually don't help in LIKE queries (i.e. they require a table scan), so there's a good reason to avoid them even when they do work. Other people's experience may be different, but the only wildchar I've ever used in a LIKE query is '%'.
Maurits [MSFT] on 28 Nov 2006 11:23 AM:
A couple of things that bug me...
LIKE doesn't support including ] in a character class unless you use the strange ESCAPE syntax
Full-text search doesn't consider < or > to be word-breakers, which makes it hard to keyword-search HTML documents
I have found myself using LIKE in WHERE clauses of stored procedures:
WHERE
'%, ' + @IDS + ', %' LIKE '%, ' + CAST(ID AS VARCHAR(20)) + ', %'
but only for small tables, and never when performance is important.
referenced by