TSQL UDF To Split String Every 8 Characters

Someone decided to stuff a bunch of times together into a single column, so the column value might look like this:

08:00 AM01:00 PM

And another column contains the date in the following format;

20070906

I want to write a UDF to normalize this data in a single SQL query, so I can get back 2 rows of datetime type for the above example

2007-09-06 08:00:00.000
2007-09-06 13:00:00.000

The conversion to datetime type is simple...but I need to split the time part every 8 characters to get the individual time ou开发者_开发百科t.

Anyone know of an existing UDF to do this?

Try this, it'll split your string into chunks of the specified lenth:

create function SplitString
(   
    @str varchar(max),
    @length int
)
returns @Results table( Result varchar(50) ) 
AS
begin
    declare @s varchar(50)
    while len(@str) > 0
    begin
        set @s = left(@str, @length)
        set @str = right(@str, len(@str) - @length)
        insert @Results values (@s)
    end
    return 
end

For example:

select * from dbo.SplitString('08:00 AM01:00 PM', 8)

Will give this result:

Result

08:00 AM

01:00 PM

There is a bug in the query above, the below query fixes this. Also, I have made the returned table contain a sequence column so that it is possible to determine what sequence the split is in:

CREATE function SplitString
(   
    @str varchar(max),
    @length int
)
RETURNS @Results TABLE( Result varchar(50),Sequence INT ) 
AS
BEGIN

DECLARE @Sequence INT 
SET @Sequence = 1

    DECLARE @s varchar(50)
    WHILE len(@str) > 0
    BEGIN
        SET @s = left(@str, @length)
        INSERT @Results VALUES (@s,@Sequence)

        IF(len(@str)<@length)
        BREAK

        SET @str = right(@str, len(@str) - @length)
        SET @Sequence = @Sequence + 1
    END
    RETURN 
END