December 1, 2010 at 11:14 am
Oleg Netchaev (12/1/2010)
Stefan Krzywicki (12/1/2010)
I wasn't saying he was wrong, I was looking for clarification since going for a NOT NOT seems an odd way to go about it. So
NOT LIKE '%[^0-9]%'means where nothing is not like 0-9
Check every character (that's the %...%)
If it is not like 0-9 (that's the ^)
Then it isn't numeric (that's the NOT LIKE)
If even one character is LIKE instead of NOT LIKE, then don't return the record.
I think part of the confusing aspect is that LIKE with a %...% will return any record with even one character that is like, but NOT LIKE %..% will only return records where not a single character is like. There's 2 things you have to flip in your head when thinking about it.
All I was trying to point out was that your original suggestion to use LIKE '%[0-9]%' instead is not going to work, that is all.
-- Jeff's
NOT LIKE '%[^0-9]%'
-- is not equivalent to your
LIKE '%[0-9]%'
These are totally different predicates. The former by Jeff correctly matches the input values consisting of any number of any digits.
The latter you suggested does not do the same. It does not have double negatives, but does not really work either, because it matches input values consisting of any number of any characters as long as the input includes one digit (actually one or more because every next digit also satisfies any character pattern)
Oleg
Yes, I know, that's what I'm saying. I was trying to restate the NOT LIKE '%[^0--9]%' in english descriptions that would clarify for me exactly what it is doing. I was originally confused by why you need the double negative in there and was trying to "talk it out" step by step to make sure I understood why the NOT LIKE works and the LIKE doesn't.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
December 1, 2010 at 11:23 am
Stefan Krzywicki (12/1/2010)
Yes, I know, that's what I'm saying. I was trying to restate the NOT LIKE '%[^0--9]%' in english descriptions that would clarify for me exactly what it is doing. I was originally confused by why you need the double negative in there and was trying to "talk it out" step by step to make sure I understood why the NOT LIKE works and the LIKE doesn't.
The LIKE %[0-9]% will search for any numeric in the string, return a positive if it exists at all.
The NOT LIKE %^[0-9]%:
Search for any non-integer character. Return a positive for non-integers. Invert it (Not like). This means that if it finds a non-integer, it will return positive (everything BUT an integer), then it will only pull records that fail this test.
Takes a few reads to get used to double negating. Most folks don't use it often enough to remember why. 🙂
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
December 1, 2010 at 12:19 pm
Perhaps I'm missing something, but doesn't the following do the same thing in a more straightforward way (without the double negatives)?
somecolumn like '%[0-9]%'
December 1, 2010 at 12:25 pm
JRUDE (12/1/2010)
Perhaps I'm missing something, but doesn't the following do the same thing in a more straightforward way (without the double negatives)?somecolumn like '%[0-9]%'
Go back and re-read the last two pages. Stefan had the same question. If you still don't understand the issue then, please let us know and we'll rephrase the answers.
December 1, 2010 at 12:27 pm
JRUDE (12/1/2010)
Perhaps I'm missing something, but doesn't the following do the same thing in a more straightforward way (without the double negatives)?somecolumn like '%[0-9]%'
If you read my comment and the responses to it, you'll see why it doesn't work. I had the same thought.
The LIKE '%[0-9]%' will return any string that has any number in it anywhere.
The NOT LIKE '%[^0-9]%' will return any string that has only numbers in it.
This is because it creates a set of all non-number characters and only returns strings where no part of the string matches anything in the set of non-number characters.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
December 1, 2010 at 12:29 pm
Brandie Tarvin (12/1/2010)
JRUDE (12/1/2010)
Perhaps I'm missing something, but doesn't the following do the same thing in a more straightforward way (without the double negatives)?somecolumn like '%[0-9]%'
Go back and re-read the last two pages. Stefan had the same question. If you still don't understand the issue then, please let us know and we'll rephrase the answers.
Heh, I'll probably be rephrasing the answer to myself a few hundred times trying to get the clearest phrasing. : -) I want it to be stated in a way where I'll remember it clearly later.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
December 1, 2010 at 12:31 pm
My apologies. I had not read through all of the responses prior to posting my reply. Your explanation was very clear. Thank you for being kind.
December 1, 2010 at 12:32 pm
Stefan Krzywicki (12/1/2010)
The LIKE '%[0-9]%' will return any string that has any number in it anywhere.
The NOT LIKE '%[^0-9]%' will return any string that has only numbers in it.
This is because it creates a set of all non-number characters and only returns strings where no part of the string matches anything in the set of non-number characters.
Speaking of rephrasing... For that last sentence, how about...
The NOT LIKE '%[^0-9]%' will return any string that has only numbers in it because it creates a set of all non-number characters and drops any records that contain characters from that set.
December 1, 2010 at 12:33 pm
JRUDE (12/1/2010)
My apologies. I had not read through all of the responses prior to posting my reply. Your explanation was very clear. Thank you for being kind.
No problem. Anytime something doesn't make sense, just ask us to rephrase it. We're happy to give the 101 different methods of saying the same thing. @=)
December 1, 2010 at 12:35 pm
JRUDE (12/1/2010)
My apologies. I had not read through all of the responses prior to posting my reply. Your explanation was very clear. Thank you for being kind.
Happy to help. After Oleg set me straight on it I needed to go through it a few times myself to make sure I understood.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
December 1, 2010 at 12:35 pm
Brandie Tarvin (12/1/2010)
Stefan Krzywicki (12/1/2010)
The LIKE '%[0-9]%' will return any string that has any number in it anywhere.
The NOT LIKE '%[^0-9]%' will return any string that has only numbers in it.
This is because it creates a set of all non-number characters and only returns strings where no part of the string matches anything in the set of non-number characters.
Speaking of rephrasing... For that last sentence, how about...
The NOT LIKE '%[^0-9]%' will return any string that has only numbers in it because it creates a set of all non-number characters and drops any records that contain characters from that set.
More concise, I like it.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
December 1, 2010 at 12:53 pm
CirquedeSQLeil (11/30/2010)
Jeff Moden (11/30/2010)
CirquedeSQLeil (11/30/2010)
Good stuff Jeff. Got that spackle article out pretty quick.Thanks, Jason. Heh... When Steve first came out with the "Requested Articles" forum, I banged out 2 "spackles" that night and 2 more the next night. From the looks of the expected publish dates he's assigned on my contribution page, it looks like he has me scheduled every Wednesday for about 4 weeks. The first one came out on Nov 15th.
I must have missed it. What is the link?
If you mean the link for the Requested Articles forum, it's here.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
December 1, 2010 at 1:05 pm
WayneS (12/1/2010)
CirquedeSQLeil (11/30/2010)
Jeff Moden (11/30/2010)
CirquedeSQLeil (11/30/2010)
Good stuff Jeff. Got that spackle article out pretty quick.Thanks, Jason. Heh... When Steve first came out with the "Requested Articles" forum, I banged out 2 "spackles" that night and 2 more the next night. From the looks of the expected publish dates he's assigned on my contribution page, it looks like he has me scheduled every Wednesday for about 4 weeks. The first one came out on Nov 15th.
I must have missed it. What is the link?
If you mean the link for the Requested Articles forum, it's here.
Nah, I was looking for his previous article.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
December 1, 2010 at 1:32 pm
I have also run into the same problem with ISNUMERIC() that many people here have mentioned in terms of wanting something more like "IsReallyNumeric". So, I created a SQLCLR function for my SQL# (SQLsharp) library called String_IsNumeric. I tried to account for various numeric notations that use either commas or spaces to separate the "thousands", allowing either comma or period to denote a decimal, and allowing various monetary symbols at the left or right of the number. If anyone is interested, you can download this for free at: http://www.SQLsharp.com/. A full explanation of the number formats is available in the PDF manual (also on the Donwload page) on page 18 (page 24 of the PDF).
Take care,
Solomon...
SQL# — https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
Sql Quantum Lift — https://SqlQuantumLift.com/ ( company )
Sql Quantum Leap — https://SqlQuantumLeap.com/ ( blog )
Info sites — Collations • Module Signing • SQLCLR
December 1, 2010 at 1:33 pm
CirquedeSQLeil (12/1/2010)
WayneS (12/1/2010)
CirquedeSQLeil (11/30/2010)
Jeff Moden (11/30/2010)
CirquedeSQLeil (11/30/2010)
Good stuff Jeff. Got that spackle article out pretty quick.Thanks, Jason. Heh... When Steve first came out with the "Requested Articles" forum, I banged out 2 "spackles" that night and 2 more the next night. From the looks of the expected publish dates he's assigned on my contribution page, it looks like he has me scheduled every Wednesday for about 4 weeks. The first one came out on Nov 15th.
I must have missed it. What is the link?
If you mean the link for the Requested Articles forum, it's here.
Nah, I was looking for his previous article.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
Viewing 15 posts - 46 through 60 (of 168 total)
You must be logged in to reply to this topic. Login to reply