# Thinking outside the box

Patron Saint of Lost Yaks
posts - 203, comments - 734, trackbacks - 4

## December 2007 Blog Posts

##### Algorithm to sort strings mixed with Alpha and Numeric values.

CREATE FUNCTION dbo.fnSplitType (         @Data VARCHAR(200),         @PartSize TINYINT ) RETURNS VARCHAR(8000) AS BEGIN         DECLARE @Result VARCHAR(8000),                 @Alpha TINYINT,                 @OldPosition SMALLINT,                 @NewPosition SMALLINT         SELECT  @Result = '',                 @Alpha = 1,                 @OldPosition = 1,                 @NewPosition = 1         IF @Data LIKE '[0-9]%'                 SELECT  @Result = REPLICATE(' ', @PartSize),                         @Alpha = 0         WHILE @NewPosition < LEN(@Data)                 SELECT  @NewPosition =  CASE @Alpha                                                 WHEN 1 THEN PATINDEX('%[0-9]%', SUBSTRING(@Data, @OldPosition, 8000))                                                 ELSE PATINDEX('%[a-z]%', SUBSTRING(@Data, @OldPosition, 8000))                                         END,                         @NewPosition =  CASE @NewPosition                                                 WHEN 0 THEN LEN(@Data)                                                 ELSE @OldPosition + @NewPosition - 2                                         END,                         @Result = @Result +     CASE @Alpha                                                         WHEN 1 THEN LEFT(LTRIM(SUBSTRING(@Data, @OldPosition, @NewPosition - @OldPosition + 1)) + REPLICATE(' ', @PartSize), @PartSize)                                                         ELSE RIGHT(REPLICATE(' ', @PartSize) + RTRIM(SUBSTRING(@Data, @OldPosition, @NewPosition - @OldPosition + 1)), @PartSize)                                                 END,                         @Alpha = 1 - @Alpha,                         @OldPosition = @NewPosition + 1         RETURN  RTRIM(@Result) END Here is...

posted @ Wednesday, December 19, 2007 2:20 PM | Feedback (4) | Filed Under [ SQL Server 2008 Algorithms SQL Server 2005 SQL Server 2000 ]

##### Thanks Jon!

I didn't read this until I noticed Mladens link. http://weblogs.sqlteam.com/jhermiz/archive/2007/12/17/What-If-The-Dream-Company.aspx

posted @ Monday, December 17, 2007 9:05 PM | Feedback (1) | Filed Under [ SQL Server 2008 SQL Server 2005 SQL Server 2000 ]

##### Cursor is really faster than set-based solution for weighted moving average?

Today, I was involved in an interesting discussion. Someone asked for a moving average solution. I joined the discussion late. The previous solutions and mine were all set-based and very slow. Then Jezemine come up with a CURSOR solution that looked fine and fast. After some trial-and-errors I finally posted a set-based solution that seems to be the fastest solution yet. http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=93911 So it still seems that [proper implemented] set-based solutions are the fastest, compared to CURSORs.  Below is also an implementation of a SQL Server 2005 approach.   DECLARE     @Sample TABLE (dt DATETIME, Rate FLOAT)   INSERT      @Sample SELECT      CURRENT_TIMESTAMP - 10, 1 UNION ALL SELECT      CURRENT_TIMESTAMP - 9, 2 UNION ALL SELECT      CURRENT_TIMESTAMP - 8, 4...

posted @ Monday, December 10, 2007 8:20 PM | Feedback (3) | Filed Under [ Optimization SQL Server 2008 Algorithms SQL Server 2005 SQL Server 2000 ]