CREATE FUNCTION dbo.fnSplitType
DECLARE @Result VARCHAR(8000),
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))
@NewPosition = CASE @NewPosition
WHEN 0 THEN LEN(@Data)
ELSE @OldPosition + @NewPosition - 2
@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)
@Alpha = 1 - @Alpha,
@OldPosition = @NewPosition + 1
I didn't read this until I noticed Mladens link.
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.
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)
SELECT CURRENT_TIMESTAMP - 10, 1 UNION ALL
SELECT CURRENT_TIMESTAMP - 9, 2 UNION ALL
SELECT CURRENT_TIMESTAMP - 8, 4...