Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

Wildcards Expand / Collapse
Author
Message
Posted Friday, November 22, 2013 7:18 AM


Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, February 27, 2014 7:06 AM
Points: 7, Visits: 7
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?
Post #1516774
Posted Friday, November 22, 2013 12:40 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:20 AM
Points: 7,696, Visits: 9,424
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 &.





Tom
Post #1516907
Posted Friday, November 22, 2013 1:37 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 4:16 PM
Points: 31,018, Visits: 15,456
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.

[] 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.

; with mycte (mychar)
as
(select mychar = 'Steve'
union
select mychar = 'Bill'
union
select mychar = 'Stephanie'
union
select mychar = 'Adam'
)
select mychar
from mycte
where mychar like '[S]%'

"&" 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.







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1516924
Posted Saturday, November 23, 2013 3:56 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 3:38 AM
Points: 1,263, Visits: 601
For 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.

http://msdn.microsoft.com/en-us/library/069b0htd(v=vs.110).aspx

hmmm, interesting.
Post #1516999
Posted Saturday, November 23, 2013 9:23 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, September 15, 2014 11:49 AM
Points: 1,848, Visits: 587
nice and easy..
Thanks Steve..
Post #1517014
Posted Saturday, November 23, 2013 9:32 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, September 15, 2014 11:49 AM
Points: 1,848, Visits: 587
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..
Post #1517015
Posted Saturday, November 23, 2013 9:49 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, September 18, 2014 1:36 PM
Points: 6,002, Visits: 8,267
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.



Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #1517016
Posted Saturday, November 23, 2013 5:26 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:20 AM
Points: 7,696, Visits: 9,424
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
Post #1517054
Posted Friday, November 29, 2013 7:03 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 1:42 PM
Points: 465, Visits: 376
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.
Post #1518547
Posted Saturday, November 30, 2013 6:04 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 4:16 PM
Points: 31,018, Visits: 15,456
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.







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1518671
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse