November 23, 2013 at 9:32 am
L' Eomot Inversé (11/22/2013)
Bad question because the words you were wrote don't necessarily mean what you apparently think they do. Or was it intentionally a trick question?Mind you, it's not as bad as Tuesday's question, which is unambiguously wrong and ought to be corrected (there are only about a dozen comments so far; they all say it's wrong;
which of these wildcard characters should be followed by a list of wildcard characters; but the list contains &, which isn't a wildcard character, {} which isn't a wild card character (it's two characters), and [] which isn't a wild card character (it too is two characters). You might want to say that you didnt mean a list of single wildcard characters, but if you mant a list of strings of wildcard characters then the presence of {} and & means the list doesn't match the question; doesn't match anything at all makes the answer wrong. So we are left guessing what you mean. I guessed you meant strings of characters, since you gave a list of strings and not a list of characters or a list of wildcard strings. And the string [] can't match a single character - indeed it can't match anything at all unless preceeded by an escape character and then it matches 2 characters.
[] can't match a single character: s like '[]' is always false (unless s is null, in which case it's unknown).
{} can't natch a single character, it always matches two.
_ matches any single character, and can only match a single character.
& matches a particular single character (itself), and can't match anything else.
% matches any single character, but also matches any string.
So there are three strings which can match a single character; but only two strings are to be selected; two of them can only match a single character, the third can match other things as well, so the choice is obvious: the right answer is _ and &.
the question sounds pretty clear to me..
November 23, 2013 at 9:49 am
gerry anderson (11/22/2013)
I gave the example a try, mychar LIKE '[a-z]dam' returns only Bdam. While mychar like '[A-Z]dam' returns Adam and Bdam. Is this a result of the version/flavor of SQL?
No, it's a result of the collation you use. I'm guessing that the defauult collation for the column you tested this on is case sensitive. In most case sensitive collations, capitals sort first. So A comes first, then a, then B, then b, etc. That's why 'A' is included in [A-Z] but not in [a-z] - because the capital A comes before the small a, which is the starting point for the second LIKE patters.
November 23, 2013 at 5:26 pm
Steve Jones - SSC Editor (11/22/2013)
Tom,You're being pedantic here in terms of the question. The question isn't which of these are single characters, which which wildcards match.
Yes, I'm probably being pedantic. But I suspect anyone else with a postgrad degree in mathematical logic would have picked the answere _ and &, just as I did.
"&" isn't a wildcard, therefore it doesn't count.
You are the one who included {} and & in what you called "the following wildcard characters", not me. And show me the single character that is matched by []? I can't imagine <string> LIKE '[]' ever returning TRUE, or <string1> LIKE <string2>[]<string3> if you want a bigger pattern containing your "wildcard".
% doesn't match single characters, it matches multiple characters. There could be a reasonable argument here that if the data contains a single character, this matches, but in general, this isn't designed to, nor is code written with this to, match a single character. I'll reword the question to remove this.
Well, it's quite clear that it can match a single character but as I said, that one doesn't matter because it can match multiple chracters so most peole won't count it. But it's probably just as well to reword to avoid that one. So I guess we agree on that part of it.
You are treating these as literals, not as the wildcard characters.
I'm treating them as things that can occur in a string which is used as a pattern. I can't treat your list of wildcard characters as wildcard characters because two of your list ({} and &) have nothing to do with wildcards, so I have to assume they are just things that might occur in a pattern. _ is a pattern component that matches a single character. So are most other single characters, including &.
But I don't think the question needs changing - the results look as if most people understood it the way you intended it. I just want to persuade you to be more careful with wording in future.
Tom
November 29, 2013 at 7:03 am
Tom may be Pedantic but I agree with him in general. Whether considered a wild card or not "[a]" is clearly not a character. Despite BOL (see the article for "LIKE") and Steve, the word "Character" is generally understood to be singular and means one character so "[a]" is not a character it is a character string.
As an aside, one of the most common sources of error in our business is misunderstandings attributable to imprecise use of language. This is a trap we all fall in. Whether we are on the specifying side or the interpreting side we need to seek clarity whenever we suspect the possibility of confusion.
November 30, 2013 at 6:04 pm
I still don't agree with you. I asked:
"which of the following wildcard characters"
To me that implies that [] is a wildcard set of characters. I think asked
"can match a single character"
Meaning, can I use one or more of the wildcards to match a single character.
Most people understood. Perhaps we're all wrong, but I fail to see how the question isn't clear unless you are trying to read the letters without realizing the spirit.
November 30, 2013 at 6:58 pm
Steve Jones - SSC Editor (11/30/2013)
I still don't agree with you. I asked:"which of the following wildcard characters"
To me that implies that [] is a wildcard set of characters. I think asked
"can match a single character"
Meaning, can I use one or more of the wildcards to match a single character.
Well, the natural answer to that is that you can't use [] to match any character. [a] can match something, can, and [cde] can, and so on, but none of those were on your list; so presumably we have to rule out & and {} because they aren't sets of wildcards, so why don't we have to rule out [a] and and [cde] and so on for the same reason? There seem to be several apparently good reasons to interpret your words as meaning something you didn't intend (and you certainly fooled me).
Most people understood. Perhaps we're all wrong, but I fail to see how the question isn't clear unless you are trying to read the letters without realizing the spirit.
I'm not saying that you and the people that understood you straight away are wrong, and I don't think Ray Herring was saying that either - in fact I think you are being silly when you interpret his post that way. However, think about some of the people who post on this site and clearly struggle with English; someone struggling with a foreign language will often take a literal meaning of the words even when it's immediately obvious to a native speaker that the words are not intended to have that precise meaning. What I want to say is that you perhaps ought to take more care not to make difficulties for people who already have a problem because English is foreign to them. I'm lucky, I don't have a problem, I don't struggle, but I still misunderstood because I took your words literally.
Tom
December 1, 2013 at 3:42 am
L' Eomot Inversé (11/30/2013)
Well, the natural answer to that is that you can't use [] to match any character. [a] can match something, can, and [cde] can, and so on, but none of those were on your list
But [a], , and [cde] are all wildcard patterns - a search pattern that combines wildcard characters and normal characters to define the set of strings that should match.
The question was not about wildcard patters (or, to stick to the official Microsoft terminology, "like patterns"). The question was about wildcard characters. Of which exactly four are described in Books Online: %, _, [], and [^]. See http://technet.microsoft.com/en-us/library/ms179859.aspx.
December 1, 2013 at 5:54 am
Hugo Kornelis (12/1/2013)
But [a], , and [cde] are all wildcard patterns - a search pattern that combines wildcard characters and normal characters to define the set of strings that should match.The question was not about wildcard patters (or, to stick to the official Microsoft terminology, "like patterns"). The question was about wildcard characters. Of which exactly four are described in Books Online: %, _, [], and [^]. See http://technet.microsoft.com/en-us/library/ms179859.aspx.
So, [] is a character, and so is [^]? :w00t: That's quite an amazing misuse of English! Neither one is a character. One of them consists of two characters, and the other consists of three characters. Is the whole text of Gibbon's "Decline and Fall of the Roman Empire" a character? After all, if a string (or pattern, fo that matter) of two characters is a character and so is a string of three characters, it's difficult to see why a string of tvery very many characters shouldn't also be a character as well!
If the question was not about patterns but about those "character"s, please tell me what you think the "character" [] matches, as opposed to what can be matched by a pattern which includes the two real characters that occur in that "character" as well as some other characters placed between them. I know very well that X LIKE '[]'returns false unless X is NULL (in which case it returns unknown when the settings are ANSI-conformant, and false otherwise), but apparently you believe something different.
Tom
December 1, 2013 at 7:27 am
L' Eomot Inversé (12/1/2013)
So, [] is a character, and so is [^]? :w00t: That's quite an amazing misuse of English!
This isn't an English exam, it's a question about SQL Server. And the SQL Server documentation dubs [] and [^] characters. Deal with it, or file a documentation bug.
By the way, this is not even unique. It is in fact quite common to document elements of wildcard-featurinig search patterns "characters", even if they consist of multiple characters. Check for instance this page I found with official documentation for regexp (regular expressions): http://www.regular-expressions.info/refcharacters.html.
If the question was not about patterns but about those "character"s, please tell me what you think the "character" [] matches
It matches exactly one of the literal characters that is placed between the opening and closing bracket. Next time I post a link, please follow it and read it instead of bothering me with embarassing questions.
as opposed to what can be matched by a pattern which includes the two real characters that occur in that "character" as well as some other characters placed between them.
Ah, so you DO know what is matched by using the [] character in the appropriate way in a LIKE pattern. Then why ask? If you are really too stubborn to admit that you were wrong, then just unsubscribe from the topic and move on.
I know very well that X LIKE '[]'returns false unless X is NULL (in which case it returns unknown when the settings are ANSI-conformant, and false otherwise), but apparently you believe something different.
Please don't tell me what I do or do not believe, and please stop trying to sneak the discussion back to patterns - they are not the issue of this question.
December 2, 2013 at 11:25 pm
Very straightforward. Thanks for making life easy Steve. 🙂
December 20, 2013 at 2:51 am
Probably did I not understand this ENGLISH question but I have had a good answer! 😛
Viewing 11 posts - 16 through 25 (of 25 total)
You must be logged in to reply to this topic. Login to reply