SQLServerCentral.com / Discuss Content Posted by Steve Jones / Article Discussions by Author / Article Discussions / Wildcards / Latest PostsInstantForum.NET v99.99.99SQLServerCentral.comhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comFri, 09 Dec 2016 20:21:24 GMT20RE: Wildcardshttp://www.sqlservercentral.com/Forums/Topic1516317-32-1.aspxProbably did I not understand this ENGLISH question but I have had a good answer! :-PFri, 20 Dec 2013 02:51:29 GMTjfgoudeRE: Wildcardshttp://www.sqlservercentral.com/Forums/Topic1516317-32-1.aspxVery straightforward. Thanks for making life easy Steve. :-)Mon, 02 Dec 2013 23:25:51 GMTsqlnaiveRE: Wildcardshttp://www.sqlservercentral.com/Forums/Topic1516317-32-1.aspx[quote][b]L' Eomot Inversé (12/1/2013)[/b][hr]So, [] is a character, and so is [^]? :w00t: That's quite an amazing misuse of English![/quote]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): [url=http://www.regular-expressions.info/refcharacters.html]http://www.regular-expressions.info/refcharacters.html[/url].[quote]If the question was not about patterns but about those "character"s, please tell me what you think the "character" [] matches[/quote]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.[quote]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.[/quote]Ah, so you [b][i]DO[/i][/b] 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.[quote]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.[/quote]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.Sun, 01 Dec 2013 07:27:11 GMTHugo KornelisRE: Wildcardshttp://www.sqlservercentral.com/Forums/Topic1516317-32-1.aspx[quote][b]Hugo Kornelis (12/1/2013)[/b][hr]But [a], [b], and [cde] are all wildcard [i]patterns[/i] - 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 [i]characters[/i]. Of which exactly four are described in Books Online: %, _, [], and [^]. See [url=http://technet.microsoft.com/en-us/library/ms179859.aspx]http://technet.microsoft.com/en-us/library/ms179859.aspx[/url].[/quote]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.Sun, 01 Dec 2013 05:54:42 GMTTomThomsonRE: Wildcardshttp://www.sqlservercentral.com/Forums/Topic1516317-32-1.aspx[quote][b]L' Eomot Inversé (11/30/2013)[/b][hr]Well, the natural answer to that is that you can't use [] to match any character. [a] can match something, [b] can, and [cde] can, and so on, but none of those were on your list[/quote]But [a], [b], and [cde] are all wildcard [i]patterns[/i] - 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 [i]characters[/i]. Of which exactly four are described in Books Online: %, _, [], and [^]. See [url=http://technet.microsoft.com/en-us/library/ms179859.aspx]http://technet.microsoft.com/en-us/library/ms179859.aspx[/url].Sun, 01 Dec 2013 03:42:31 GMTHugo KornelisRE: Wildcardshttp://www.sqlservercentral.com/Forums/Topic1516317-32-1.aspx[quote][b]Steve Jones - SSC Editor (11/30/2013)[/b][hr]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.[/quote]Well, the natural answer to that is that you can't use [] to match any character. [a] can match something, [b] 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 [b] 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). [quote]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.[/quote]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.Sat, 30 Nov 2013 18:58:37 GMTTomThomsonRE: Wildcardshttp://www.sqlservercentral.com/Forums/Topic1516317-32-1.aspxI 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.Sat, 30 Nov 2013 18:04:44 GMTSteve Jones - SSC EditorRE: Wildcardshttp://www.sqlservercentral.com/Forums/Topic1516317-32-1.aspxTom 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.Fri, 29 Nov 2013 07:03:30 GMTRay HerringRE: Wildcardshttp://www.sqlservercentral.com/Forums/Topic1516317-32-1.aspx[quote][b]Steve Jones - SSC Editor (11/22/2013)[/b][hr]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.[/quote]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.[quote]"&" isn't a wildcard, therefore it doesn't count.[/quote]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 &lt;string&gt; LIKE '[]' ever returning TRUE, or &lt;string1&gt; LIKE &lt;string2&gt;[]&lt;string3&gt; if you want a bigger pattern containing your "wildcard".[quote]% 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.[/quote]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.[quote]You are treating these as literals, not as the wildcard characters.[/quote]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.Sat, 23 Nov 2013 17:26:41 GMTTomThomsonRE: Wildcardshttp://www.sqlservercentral.com/Forums/Topic1516317-32-1.aspx[quote][b]gerry anderson (11/22/2013)[/b][hr]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?[/quote]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.Sat, 23 Nov 2013 09:49:39 GMTHugo KornelisRE: Wildcardshttp://www.sqlservercentral.com/Forums/Topic1516317-32-1.aspx[quote][b]L' Eomot Inversé (11/22/2013)[/b][hr]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 &.[/quote]the question sounds pretty clear to me..Sat, 23 Nov 2013 09:32:19 GMTpchiragsRE: Wildcardshttp://www.sqlservercentral.com/Forums/Topic1516317-32-1.aspxnice and easy..Thanks Steve..Sat, 23 Nov 2013 09:23:17 GMTpchiragsRE: Wildcardshttp://www.sqlservercentral.com/Forums/Topic1516317-32-1.aspxFor me, I am always using % in LIKE statements, although there is a minor difference which is:% means 0 or more characters in this position._ means 1 character only @ that position.[url] http://msdn.microsoft.com/en-us/library/069b0htd(v=vs.110).aspx [/url]hmmm, interesting.Sat, 23 Nov 2013 03:56:09 GMTHany HelmyRE: Wildcardshttp://www.sqlservercentral.com/Forums/Topic1516317-32-1.aspxTom,You're being pedantic here in terms of the question. The question isn't which of these are single characters, which which wildcards match.[] is a set of wildcard characters. This can be used to match a single character. You do need to provide a character to match, but this does work.[code="sql"]; with mycte (mychar)as(select mychar = 'Steve' union select mychar = 'Bill' unionselect mychar = 'Stephanie' unionselect mychar = 'Adam' )select mychar from mycte where mychar like '[S]%'[/code]"&" isn't a wildcard, therefore it doesn't count.% 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.You are treating these as literals, not as the wildcard characters.Fri, 22 Nov 2013 13:37:07 GMTSteve Jones - SSC EditorRE: Wildcardshttp://www.sqlservercentral.com/Forums/Topic1516317-32-1.aspxBad 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 &.Fri, 22 Nov 2013 12:40:24 GMTTomThomsonRE: Wildcardshttp://www.sqlservercentral.com/Forums/Topic1516317-32-1.aspxI 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?Fri, 22 Nov 2013 07:18:18 GMTgerry andersonRE: Wildcardshttp://www.sqlservercentral.com/Forums/Topic1516317-32-1.aspxNice question....Thu, 21 Nov 2013 22:12:20 GMTBanglaRE: Wildcardshttp://www.sqlservercentral.com/Forums/Topic1516317-32-1.aspxNice and easy. Thanks, Steve!Thu, 21 Nov 2013 10:02:11 GMTRevenantRE: Wildcardshttp://www.sqlservercentral.com/Forums/Topic1516317-32-1.aspx[quote][b]steve.jacobs (11/21/2013)[/b][hr]Good question. Man, I have not used the "_" in a while so I almost got it wrong. Thankfully my brain kicked in.:-DThanks for the question.[/quote]+1 Same here. I really had dive deep into the memory banks on this one.Thu, 21 Nov 2013 07:19:30 GMTDana MedleyRE: Wildcardshttp://www.sqlservercentral.com/Forums/Topic1516317-32-1.aspxGood question. Thanks Steve.Thu, 21 Nov 2013 06:39:21 GMTTee TimeRE: Wildcardshttp://www.sqlservercentral.com/Forums/Topic1516317-32-1.aspxNice simple one, thanks Steve.Thu, 21 Nov 2013 06:26:58 GMTStewart "Arturius" CampbellRE: Wildcardshttp://www.sqlservercentral.com/Forums/Topic1516317-32-1.aspxGood question. Man, I have not used the "_" in a while so I almost got it wrong. Thankfully my brain kicked in.:-DThanks for the question.Thu, 21 Nov 2013 06:24:59 GMTsteve.jacobsRE: Wildcardshttp://www.sqlservercentral.com/Forums/Topic1516317-32-1.aspxTo nitpick. For '[]' to be valid as an answer SQL should have allowed something like [code="sql"]mychar LIKE '[]dam' [/code]But this offcourse doesnt work. '[]' only work in conjunction with other input. In this case 'a-z'... which offcourse means that if one of the examples had started with a "1" for instance... it would not have been found. Also some international characters wouldnt have worked with 'a-z' either. Whereas '_' works as is. /TThu, 21 Nov 2013 05:56:39 GMTtommyhRE: Wildcardshttp://www.sqlservercentral.com/Forums/Topic1516317-32-1.aspxEasy one. Thanks Steve.Thu, 21 Nov 2013 00:34:52 GMTKoen VerbeeckRE: Wildcardshttp://www.sqlservercentral.com/Forums/Topic1516317-32-1.aspxNice straight forward question. ThanksThu, 21 Nov 2013 00:06:28 GMTFord FairlaneWildcardshttp://www.sqlservercentral.com/Forums/Topic1516317-32-1.aspxComments posted to this topic are about the item [B]<A HREF="/questions/T-SQL/104529/">Wildcards</A>[/B]Wed, 20 Nov 2013 22:22:04 GMTSteve Jones - SSC Editor