|
|
|
SSC-Enthusiastic
 
Group: General Forum Members
Last Login: Today @ 2:54 AM
Points: 128,
Visits: 926
|
|
Manie Verster (5/9/2009)
Thanks to all for jumping in to help me but that was not quite the reason for me posting the code. I thought Ha! let's see how they figure this one out and was convinced that no-one would be able to do it. Well, with my hat in my hand I have to humbly say: It can be done! Well done gserdijn and Jeff and Jeff, you taught me something today again! I am a bit puzzled with your code though and maybe you can clear something for me.
LIKE '[^- .''A-Z]'
You only put A-Z and not also a-z. How does that work? I have another cursor that I would like to change to set based and this code might help me to that. I might come back here to ask for some help.
Text comparisons in SQL are done by the rules of a collation...the default collation on installation is case insensitive, so A-Z will also match lowercase characters. You can specify a collation at the server, database and column level as well as override it in comparisons performed in your queries. If you have two fields that have a different collation you have to specify which collation rules to follow while doing the compare. Any indexes not matching the collation used in the comparison cannot be utilized during that comparison and will not be able to help you to speed up the operation. Obviously it is best to stick to one collation and make exceptions only where they are functionally required.
It's worth checking books online on the subject of collations for anyone that has not done so already!
I hope this answer satisfies your question :)
|
|
|
|
|
SSChampion
Group: General Forum Members
Last Login: Today @ 7:05 AM
Points: 20,083,
Visits: 13,639
|
|
Manie Verster (5/9/2009)
You only put A-Z and not also a-z. How does that work?
Peter gave a pretty good description as to why. To simplify, the default for SQL Server installations is "case insensitive". That means that looking of A-Z, a-z, A-z, or a-Z will all do the same thing. If you have a "case sensitive" installation (ack!) or a case sensitive column (much better), then you'd need for my little LIKE clause to say...
LIKE '[^- .''A-Za-z]'
--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
|
|
|
|
|
SSC Eights!
Group: General Forum Members
Last Login: 2 days ago @ 12:11 AM
Points: 918,
Visits: 737
|
|
Thanks Jeff and Peter,
That explains it and I also looked it up on books online and now understands it better now.
 Manie Verster
Computer Programmer
Johannesburg
South Africa
Life is about choices.... I choose to be happy today
My Blog
|
|
|
|
|
SSChampion
Group: General Forum Members
Last Login: Today @ 7:05 AM
Points: 20,083,
Visits: 13,639
|
|
|
|
|
|
SSC Eights!
Group: General Forum Members
Last Login: Tuesday, March 09, 2010 8:49 AM
Points: 806,
Visits: 598
|
|
This thread is very educational, and it puts me into trouble: I'm busy looking at all the loops used and trying to make them SET-based. Challencing, but there is more important work to be done.
A question though. I have run Jeff's script against Adventureworks and get Frank Martjnez back. From the WHERE clause I figure that this bad character is outside the scope of the LIKE clause. If I would want to include characters with CHARCODE of 161, 162 and-so-on, how would I add those to the LIKE clause? Just adding something like 'íó'? And while we're at this, can some1 explain how this LIKE clause works? This value: 'nnn''nnn', I would think is not acceptable, it's 2 values (between each pair '') without a comma seperation.
Any1 care to explain this to me?
EDIT: I am awaiting eagerly Barry's next installment of this series!
Kind regards,
Hans Brouwer
|
|
|
|
|
Forum Newbie
Group: General Forum Members
Last Login: Friday, December 11, 2009 6:50 PM
Points: 3,
Visits: 21
|
|
It's sorely tempting to rewrite every cursor into pure SQL (the ETL I inherited is rife with them), but the key is to focus on the "long pole in the tent": only optimize what's hampering business value. Otherwise you're afflicted with CTD -- "Compulsive Tuning Disorder" (a term devised by Gaja Krishna Vaidyanatha in 2001).
http://searchoracle.techtarget.com/news/interview/0,289202,sid41_gci1075628,00.html#
The "curse of the cursors" is the bane of every RDBMS, not just SQL Server!
http://it.toolbox.com/blogs/data-ruminations/the-curse-of-the-cursor-31851
|
|
|
|
|
SSC-Enthusiastic
Group: General Forum Members
Last Login: Yesterday @ 3:11 PM
Points: 110,
Visits: 164
|
|
This was shaping to be a magnificent series, but is part 3 on the way? I really hope so.
The import routines we use for our crm system all use cursors and we have severe performance problems with housekeeping jobs and imports running almost 24*7. I'd love to convert them all and was hoping this brilliantly clearly written series would teach me how.
|
|
|
|
|
SSCrazy Eights
Group: General Forum Members
Last Login: Today @ 5:15 AM
Points: 9,104,
Visits: 8,524
|
|
I do truly want and hope to. After my hospitalization in April and May, I did finally get caught up in my work by the end of June. Then I was on vacation for a week, then a close family member passed away and I happen to be the executor of his estate. The last one is still on-going, but I am really going to try to get the next one out in August.
-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc. "Performance is our middle name."
|
|
|
|
|
Forum Newbie
Group: General Forum Members
Last Login: Thursday, February 04, 2010 7:42 AM
Points: 1,
Visits: 178
|
|
Total noob here. Love this series, can't wait for 3rd installment.
I found some code in an article today, author said "The easiest way I've found to do this is run through a cursor. Sounds painful, but you will only have to do it once..."
What he's referring to is adding a UTCOffset column to a Calendar table, then populating that column only.
CREATE TABLE dbo.Calendar
(
dt SMALLDATETIME NOT NULL
PRIMARY KEY CLUSTERED,
isWeekday BIT,
isHoliday BIT,
Y SMALLINT,
FY SMALLINT,
Q TINYINT,
M TINYINT,
D TINYINT,
DW TINYINT,
monthname VARCHAR(9),
dayname VARCHAR(9),
W TINYINT,
)
GO
Add a few years of dates dates...
INSERT Calendar(dt)
SELECT DATEADD(DAY, Number, '20000101')
FROM #Numbers
WHERE Number <= 10957
ORDER BY Number
GO
Populate the table...
UPDATE dbo.Calendar SET
isWeekday = CASE
WHEN DATEPART(DW, dt) IN (1,7)
THEN 0
ELSE 1 END,
isHoliday = 0,
Y = YEAR(dt),
FY = YEAR(dt),
/*
-- if our fiscal year
-- starts on May 1st:
FY = CASE
WHEN MONTH(dt) < 5
THEN YEAR(dt)-1
ELSE YEAR(dt) END,
*/
Q = CASE
WHEN MONTH(dt) <= 3 THEN 1
WHEN MONTH(dt) <= 6 THEN 2
WHEN MONTH(dt) <= 9 THEN 3
ELSE 4 END,
M = MONTH(dt),
D = DAY(dt),
DW = DATEPART(DW, dt),
monthname = DATENAME(MONTH, dt),
dayname = DATENAME(DW, dt),
W = DATEPART(WK, dt)
GO
Add the column...
ALTER TABLE dbo.Calendar ADD UTCOffset TINYINT NULL
GO
Use Cursor to populate UTCOffset column...
SET NOCOUNT ON
DECLARE @dt SMALLDATETIME
DECLARE @offset TINYINT
SET @offset = 5
DECLARE c CURSOR
LOCAL STATIC READ_ONLY FOR
SELECT dt FROM dbo.Calendar ORDER BY dt
OPEN c
FETCH NEXT FROM c INTO @dt
WHILE @@FETCH_STATUS = 0
BEGIN
IF DATENAME(dw, @dt)='Sunday'
AND DATEPART(DAY, @dt) <= 7
AND DATENAME(MONTH, @dt) = 'April'
SET @offset = 4
IF DATENAME(dw, @dt)='Sunday'
AND DATEPART(DAY, @dt) >= 25
AND DATENAME(MONTH, @dt) = 'October'
SET @offset = 5
UPDATE dbo.Calendar SET UTCOffset = @offset WHERE dt = @dt
FETCH NEXT FROM c INTO @dt
END
CLOSE c
DEALLOCATE c
GO
So I took a stab at converting it...
DECLARE @offset TINYINT
SELECT @offset = 5
UPDATE dbo.Calendar SET
isWeekday = CASE
WHEN DATEPART(DW, dt) IN (1,7)
THEN 0
ELSE 1 END,
isHoliday = 0,
Y = YEAR(dt),
FY = YEAR(dt),
/*
-- if our fiscal year
-- starts on May 1st:
FY = CASE
WHEN MONTH(dt) < 5
THEN YEAR(dt)-1
ELSE YEAR(dt) END,
*/
Q = CASE
WHEN MONTH(dt) <= 3 THEN 1
WHEN MONTH(dt) <= 6 THEN 2
WHEN MONTH(dt) <= 9 THEN 3
ELSE 4 END,
M = MONTH(dt),
D = DAY(dt),
DW = DATEPART(DW, dt),
monthname = DATENAME(MONTH, dt),
dayname = DATENAME(DW, dt),
W = DATEPART(WK, dt),
@offset = UTCOffset = CASE WHEN DATENAME(dw, dt)='Sunday'
THEN CASE WHEN DATEPART(DAY, dt) <= 7 THEN CASE WHEN DATENAME(MONTH, dt) = 'April'
THEN 4
ELSE @offset END
--WHEN DATENAME(dw, dt)='Sunday'
WHEN DATEPART(DAY, dt) >= 25
THEN CASE WHEN DATENAME(MONTH, dt) = 'October'
THEN 5
ELSE @offset END
ELSE @offset END
ELSE @offset END
GO
Forgive me if the code formatting is bad (remember, I said NOOB here!)
Any alternatives anyone?
Thanks Barry, GWS.
Edit: Note that the dates when the clocks change have since changed.
|
|
|
|
|
SSCrazy Eights
Group: General Forum Members
Last Login: Today @ 5:15 AM
Points: 9,104,
Visits: 8,524
|
|
|
|
|
