sql server - TSQL UDF To Split String Every 8 Characters

Question

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 out.

Anyone know of an existing UDF to do this?

Answer 1

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