Tuesday, August 30, 2005
Monday, August 29, 2005
...And here is the SplitText function modified to use the NumberTable function:
--Splits a delimited text into individual items --From http://www.sommarskog.se/arrays-in-sql.html#tblnum-unlimited CREATE FUNCTION dbo.SplitText( @List ntext, @Del nchar(1) = N',' ) RETURNS @T TABLE (Item nvarchar(4000)) AS BEGIN DECLARE @slices TABLE (slice nvarchar(4000) NOT NULL) DECLARE @slice nvarchar(4000), @Textpos int, @maxlen int, @stoppos int, @maxnum int SELECT @Textpos = 1, @maxlen = 4000 - 2, @maxnum = DATALENGTH(@List) / 2 WHILE DATALENGTH(@List) / 2 - (@Textpos - 1) >= @maxlen BEGIN SELECT @slice = SUBSTRING(@List, @Textpos, @maxlen) SELECT @stoppos = @maxlen - CHARINDEX(@Del, REVERSE(@slice)) INSERT @slices (slice) VALUES (@Del + LEFT(@slice, @stoppos) + @Del) SELECT @Textpos = @Textpos - 1 + @stoppos + 2 -- On the other side of the delimiter. END INSERT @slices (slice) VALUES (@Del + SUBSTRING(@List, @Textpos, @maxlen) + @Del) INSERT @T (Item) SELECT x.Item FROM ( SELECT Item = LTRIM(RTRIM(SUBSTRING(s.slice, N.Number + 1, CHARINDEX(@Del, s.slice, N.Number + 1) - N.Number - 1))) FROM dbo.NumberTable(1, @maxnum) N INNER JOIN @slices s ON N.Number <= LEN(s.slice) - 1 AND SUBSTRING(s.slice, N.Number, 1) = @Del ) AS x RETURN END
Erland Somarskog has some excellent articles on a few interesting SQL topics. One is titled Arrays and Lists in SQL Server, in which he covers various ways of parsing delimited strings into tables. The following is a minor modification of one of the functions he describes; I changed it from an inline function to a table function to allow setting the max value for my NumberTable function. This also allows me to create the @WrappedList parameter which simplifies the logic some:
--Fast Split function using a sequence table (provided by the UDF dbo.NumberTable) --Created 08/29/05 by Oskar Austegard from Erland Sommarskog's code at --http://www.sommarskog.se/arrays-in-sql.html#tblnum-core CREATE FUNCTION Split ( @List varchar(7998), --The delimited list @Del char(1) = ',' --The delimiter ) RETURNS @T TABLE (Item varchar(7998)) AS BEGIN DECLARE @WrappedList varchar(8000), @MaxItems int SELECT @WrappedList = @Del + @List + @Del, @MaxItems = LEN(@List) INSERT INTO @T (Item) SELECT SUBSTRING(@WrappedList, Number + 1, CHARINDEX(@Del, @WrappedList, Number + 1) - Number - 1) FROM dbo.NumberTable(1, @MaxItems) n WHERE n.Number <= LEN(@WrappedList) - 1 AND SUBSTRING(@WrappedList, n.Number, 1) = @Del RETURN END
Friday, August 26, 2005
Starting with the Microsoft article from Dr. Tom's Workshop: Generating Sequence Numbers, I modified the code to fit our SQL standards, gave it (what I think is) a more logical name and added a @Min parameter. Figuring out how to properly increment the values when the table didn't start at 1 and handling values near the limits of int was a bit tricky...
--Creates a table of sequential numbers, useful for all sorts of things --Created 08/26/05 by Oskar Austegard from article at --http://msdn.microsoft.com/library/en-us/dnsqlpro03/html/sp03k1.asp --Limits: @Min and @Max must be between -2147483647 and 2147483647, including. --If @Max <= @Min, only a single record with @Min is created ALTER FUNCTION dbo.NumberTable (@Min int, @Max int) RETURNS @T TABLE (Number int NOT NULL PRIMARY KEY) AS BEGIN -- Seed the table with the min value INSERT @T VALUES (@Min) --Loop until all the rows are created, inserting ever more records for each iteration (1, 2, 4, etc) WHILE @@ROWCOUNT > 0 INSERT @T --Get the next values by adding the current max - start value + 1 to each existing number --need to calculate increment value first to avoid arithmetic overflow near limits of int SELECT t.Number + (x.MaxNumber - @Min + 1) FROM @T t CROSS JOIN (SELECT MaxNumber = MAX(Number) FROM @T) x --Current max WHERE --Do not exceed the Max - shift the increment to the right side to take advantage of index t.Number <= @Max - (x.MaxNumber - @Min + 1) RETURN END
Thursday, August 25, 2005
"...in the next 10 years, ethanol feedstock will move from harvested corns and sugars to the vast amounts of cellulosic waste that currently are landfilled or burned. Cellulosic material -- such as rice straw and hulls, wheat straw, forestry waste, and corn stover -- are all currently problematic for farmers and foresters. However, these materials can be transformed using enzymatic processes into sugars and ligin. The sugars can be processed into ethanol, biodiesel, or other specialty chemicals, and the ligin can be used as a clean fuel source, potentially allowing the ethanol facility to use zero net energy in its operations.
"Using cellulosic waste will be critical if ethanol markets are to mature. The recently passed energy bill creates a nationally mandated threshold to 7.5 billion gallons, which would result in 5% of gasoline displaced by ethanol. If, as some estimate, our capacity to produce corn-based ethanol will max out at around 10 billion gallons, we simply will not have enough corn. Corn stover -- the stalk and leaves from corn -- along would add 7 to 12 billion gallons of ethanol capacity."
So the optimistic estimate is for corn-based ethanol (including stover) to reduce our gasoline consumption by 15%. That doesn't sound like a whole hell of a lot, but I guess it's a step in the right direction. Hopefully, as the industry matures, the tax subsidies will diminish. Yeah, right. Not as long as Iowa is one of the first primaries/caucuses...
Monday, August 22, 2005
A very good reason to not use a single common word for your password; they are that much more succeptible to a dictionary attack: GData: An Online MD5 Hash Database contains over 12 million words/phrases and their MD5 hash sequences.
For example, e338bc584bd1c7f87b8a5bf70a3cf80e was easily "cracked" as buddy in just 0.0036 seconds, and 9095eae491e5c0c17a2ef89477393ec4 just as easily looked up as puppy. On the other hand, de7b21110b84f3b84bc47b09b6d9475f, or the hash of buddy puppy, was not "cracked".
I'm not sure if this effort deserves a 35f5b296537c9229765834b5d2f37b34 or a 3021e68df9a7200135725c6331369a22.
Friday, August 19, 2005
Build 205Get it here
Note: Uninstall any of the previous builds prior to installing Build 205.
- New refactorings: Make method non-static, Safe delete
Wednesday, August 17, 2005
Monday, August 15, 2005
--Left-pads a string with a specified character to achieve a desired length --Created 08/15/05 by Oskar Austegard CREATE FUNCTION dbo.PadLeft( @String varchar(100), --Input string to be padded @Length int, --Length of final string @PadChar char(1) --Padding character ) RETURNS varchar(100) AS BEGIN WHILE LEN(@String + 'z') <= @Length SET @String = @PadChar + @String RETURN @String END GO --Right-pads a string with a specified character to achieve a desired length --Created 08/15/05 by Oskar Austegard CREATE FUNCTION dbo.PadRight( @String varchar(100), --Input string to be padded @Length int, --Length of final string @PadChar char(1) --Padding character ) RETURNS varchar(100) AS BEGIN WHILE LEN(@String + 'z') <= @Length SET @String = @String + @PadChar RETURN @String END
Friday, August 12, 2005
We could use more of these: World's Largest Solar Array to use Stirling Engine
"An area stretching for a million square kilometres across the permafrost of western Siberia is turning into a mass of shallow lakes as the ground melts, according to Russian researchers just back from the region.
The sudden melting of a bog the size of France and Germany combined could unleash billions of tonnes of methane, a potent greenhouse gas, into the atmosphere."
Thursday, August 11, 2005
An IBM project called SoulPad looks interesting - it is the natural extension of emulators and virtual machines that lets a user suspend his (or her) current desktop session (or "soul", as IBM's researchers dubbed it) while working, store the "soul" on some portable device, then restore the "soul" on a different pc, which can have a completely different hardware setup, OS, etc.
- Article: Pocket-sized computer 'soul' developed (NewScientist.com)
- Website: http://www.research.ibm.com/WearableComputing/SoulPad/soulpad.html
- Video: SoulPadVideo.wmv (14 MB)
Now what would be even nicer would be to turn this script into a firefox extension that would allow a user to filter any table on any web-page...
Also (without looking at the code) it seems you should be able to apply something similar to this to sort tables on the client?