December 9, 2011 at 8:57 am
Cadavre (12/9/2011)
Shows that Kiara's assessment is correct, so is there another reason it fails?
CHAR(1). Concatenate '%' to a char(1) that holds 'A' and it gets silently dropped. So that resulted in WHERE .. BETWEEN 'A' AND 'Z'.
That and BETWEEN doesn't do wildcards.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 9, 2011 at 9:00 am
GilaMonster (12/9/2011)
Cadavre (12/9/2011)
Shows that Kiara's assessment is correct, so is there another reason it fails?CHAR(1). Concatenate '%' to a char(1) that holds 'A' and it gets silently dropped. So that resulted in WHERE .. BETWEEN 'A' AND 'Z'.
That and BETWEEN doesn't do wildcards.
Hooray! I got part of 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 9, 2011 at 9:00 am
GilaMonster (12/9/2011)
Cadavre (12/9/2011)
Shows that Kiara's assessment is correct, so is there another reason it fails?CHAR(1). Concatenate '%' to a char(1) that holds 'A' and it gets silently dropped. So that resulted in WHERE .. BETWEEN 'A' AND 'Z'.
That and BETWEEN doesn't do wildcards.
Yes, I was aware that the % wouldn't work as a wildcard 😀
But A% is before Aaaaaaaa and Z% is after Z but not after Za, which was what myself and Kiara were saying.
Hmmm, when I execute this: -
DECLARE @StartLetter CHAR(1) = 'A', @EndLetter CHAR(1) = 'Z'
SELECT @StartLetter + '%', @EndLetter + '%'
It isn't dropping the "%", so I'd assumed that it wouldn't in the WHERE clause. Am I incorrect?
December 9, 2011 at 9:00 am
GilaMonster (12/9/2011)
Cadavre (12/9/2011)
Shows that Kiara's assessment is correct, so is there another reason it fails?CHAR(1). Concatenate '%' to a char(1) that holds 'A' and it gets silently dropped. So that resulted in WHERE .. BETWEEN 'A' AND 'Z'.
That and BETWEEN doesn't do wildcards.
LOL - I wasn't thinking about the char(1) - good thing it's not a QOTD!
I knew that between didn't do wildcards - but I obviously need more coffee and need to read more carefully this morning!
-Ki
December 9, 2011 at 9:02 am
Kiara (12/9/2011)
GilaMonster (12/9/2011)
Cadavre (12/9/2011)
Shows that Kiara's assessment is correct, so is there another reason it fails?CHAR(1). Concatenate '%' to a char(1) that holds 'A' and it gets silently dropped. So that resulted in WHERE .. BETWEEN 'A' AND 'Z'.
That and BETWEEN doesn't do wildcards.
LOL - I wasn't thinking about the char(1) - good thing it's not a QOTD!
I knew that between didn't do wildcards - but I obviously need more coffee and need to read more carefully this morning!
If you want to be flammed then I strongly suggest you post that one! :hehe:
Pre-edit. Maybe it's not as bad as I'd expect.
December 9, 2011 at 9:07 am
It seemed to be, because
Surname BETWEEN @Letter + '%' ...
and
Surname BETWEEN CAST(@Letter AS Varchar(50)) + '%' ...
produced different results. Didn't spend weeks investigating though...
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 9, 2011 at 9:08 am
Ninja's_RGR'us (12/9/2011)
Kiara (12/9/2011)
GilaMonster (12/9/2011)
Cadavre (12/9/2011)
Shows that Kiara's assessment is correct, so is there another reason it fails?CHAR(1). Concatenate '%' to a char(1) that holds 'A' and it gets silently dropped. So that resulted in WHERE .. BETWEEN 'A' AND 'Z'.
That and BETWEEN doesn't do wildcards.
LOL - I wasn't thinking about the char(1) - good thing it's not a QOTD!
I knew that between didn't do wildcards - but I obviously need more coffee and need to read more carefully this morning!
If you want to be flammed then I strongly suggest you post that one! :hehe:
Pre-edit. Maybe it's not as bad as I'd expect.
LOL - it's actually a very good question. I was falling prey to reading what I was remembering having done, not what Gail actually wrote. My person headdesk version of that only dealt with the "wildcards get ignored in between statements" thing - so that's what I was reading. 😀
-Ki
December 9, 2011 at 9:10 am
GilaMonster (12/9/2011)
It seemed to be, becauseSurname BETWEEN @Letter + '%' ...
and
Surname BETWEEN CAST(@Letter AS Varchar(50)) + '%' ...
produced different results. Didn't spend weeks investigating though...
Hm... that would imply that between pays attention to wildcards...
...heads off to test database...
-Ki
December 9, 2011 at 9:11 am
Kiara (12/9/2011)
GilaMonster (12/9/2011)
It seemed to be, becauseSurname BETWEEN @Letter + '%' ...
and
Surname BETWEEN CAST(@Letter AS Varchar(50)) + '%' ...
produced different results. Didn't spend weeks investigating though...
Hm... that would imply that between pays attention to wildcards...
...heads off to test database...
No, the latter (with the cast) produced 0 results. Not more results.
As I said, didn't spend weeks testing why it was wrong, just had to fix quickly.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 9, 2011 at 9:32 am
GilaMonster (12/9/2011)
Kiara (12/9/2011)
GilaMonster (12/9/2011)
It seemed to be, becauseSurname BETWEEN @Letter + '%' ...
and
Surname BETWEEN CAST(@Letter AS Varchar(50)) + '%' ...
produced different results. Didn't spend weeks investigating though...
Hm... that would imply that between pays attention to wildcards...
...heads off to test database...
No, the latter (with the cast) produced 0 results. Not more results.
As I said, didn't spend weeks testing why it was wrong, just had to fix quickly.
Getting different results here 😀
BEGIN TRAN
SELECT SUBSTRING('ABCDEFGHIJKLMNOPQRSTUVWXYZ',a.N,b.N-a.N+1) AS chr
INTO #temp
FROM (VALUES(1),(2),(3),(4),(5),(6),(7),(8),
(9),(10),(11),(12),(13),(14),(15),
(16),(17),(18),(19),(20),(21),(22),
(23),(24),(25),(26)) a(N)
CROSS JOIN (VALUES(1),(2),(3),(4),(5),(6),(7),(8),
(9),(10),(11),(12),(13),(14),(15),
(16),(17),(18),(19),(20),(21),(22),
(23),(24),(25),(26)) b(N)
WHERE a.N <= b.N
SELECT COUNT(*) --Returns 351
FROM #temp
DECLARE @StartLetter CHAR(1) = 'A', @EndLetter CHAR(1) = 'D'
SELECT COUNT(*) --Returns 75
FROM #temp
WHERE chr BETWEEN @StartLetter + '%' AND @EndLetter + '%'
SELECT COUNT(*) --Returns 75
FROM #temp
WHERE chr BETWEEN CAST(@StartLetter AS VARCHAR(50)) + '%' AND CAST(@EndLetter AS VARCHAR(50)) + '%'
SELECT COUNT(*) --Returns 76
FROM #temp
WHERE chr BETWEEN @StartLetter AND @EndLetter
ROLLBACK
75 returned whether the CHAR(1) is cast or not. 76 when I get rid of the %, because now "A" is included in the result-set instead of just "AB".
Sorry if it feels like I'm pestering you Gail, I just like to understand why. For me, what you're describing contradicts what I believed as the why, so I want to understand 🙂
December 9, 2011 at 9:41 am
Dev (12/9/2011)
Brandie Tarvin (12/9/2011)
DUH!Code
Code written by an experienced developer that defies all pre-existing code standards and the dev's knowledge skills by believing it can do something the developer knows is impossible.Example: Brandie's code believed it could match on NULL values, even though she knew such a thing was impossible. DUH!
I'm having one of those mornings. Someone save me now.
You forgot to post thread url... 😉
Oh, it's not a thread. I didn't post on this. I just found it and put a dent in my desk the shape of my head.
December 9, 2011 at 9:44 am
Brandie Tarvin (12/9/2011)
Dev (12/9/2011)
Brandie Tarvin (12/9/2011)
DUH!Code
Code written by an experienced developer that defies all pre-existing code standards and the dev's knowledge skills by believing it can do something the developer knows is impossible.Example: Brandie's code believed it could match on NULL values, even though she knew such a thing was impossible. DUH!
I'm having one of those mornings. Someone save me now.
You forgot to post thread url... 😉
Oh, it's not a thread. I didn't post on this. I just found it and put a dent in my desk the shape of my head.
I think I should do the same (see the bold)... :hehe:
December 9, 2011 at 10:25 am
Brandie Tarvin (12/9/2011)
Oh, it's not a thread. I didn't post on this. I just found it and put a dent in my desk the shape of my head.
Brandie?
December 9, 2011 at 10:33 am
Steve Jones - SSC Editor (12/9/2011)
Brandie Tarvin (12/9/2011)
Oh, it's not a thread. I didn't post on this. I just found it and put a dent in my desk the shape of my head.
Brandie?
Cool, now I know what she looks like! 😀
December 9, 2011 at 11:01 am
Brandie Tarvin (12/8/2011)
So with this Redmond report about IT losing jobs this year, and possibly next year, how upbeat or pessimistic are you feeling about the industry?It's hardly any wonder that IT is losing women in the industry if job numbers are static or trending down. Candidates of both genders are going to take a step back and re-assess their individual risk levels and women are a lot less likely to change careers in midstream for an industry that appears to be shrinking.
Why? Well, it's my opinion that women tend to take a long term view of where their careers will be, in addition to the money and benefits, while men tend to place the money first and worry about long term when long term walks up and smacks them upside the back of the head. I could be wrong. But I don't foresee the number of women in SQL Server rising again until the industry starts to grow again. Which does not bode well for the Women in Technology initiative.
What do you think?
What you're not catching is that the loss is for NOVEMBER. If you read the last part of the article, it mentions that IT jobs for the year are up over 37,000. Also, the jobs are just shifting sectors.
While most of the IT market will continue to slide, according to Janco Associates, the one bright spot is in healthcare IT, which is estimated to invest $40 billion in spending by the end of the year and grow by 24 percent between 2012 and 2014. This growth will arise due to new government regulations, the updating of electronic health records and the installation of modern mobile health applications.
I'd say 24% of the entire healthcare sector is a pretty fair number of jobs opening up.
But that's not as alarmist as losing (some, in one month) jobs, and therefore doesn't make the headline 😉
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
Viewing 15 posts - 32,461 through 32,475 (of 66,712 total)
You must be logged in to reply to this topic. Login to reply