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?

what's wrong with using SUBSTRING? http://msdn.microsoft.com/en-us/library/ms187748.aspx — Mitch Wheat, May 21 '11 at 02:09
EVERY 8 characters, not just 8 characters. SUBSTRING produces a single string. — Jeff, May 21 '11 at 12:40
I'm well aware of that. suggest you call it multiple times....like in a loop! — Mitch Wheat, May 21 '11 at 12:48

score 10 · Accepted Answer · answered May 21 '11 at 02:37

10

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

answered May 21 '11 at 02:37

Chris Fulstow

41,170
10
86
110

How would you suggest I go about joining this to the source table? – Jeff May 21 '11 at 12:49
Big Error. Try to set length bigger than 4 and see that is gone to unlimited calculate and can't get result. I checked it and check below @Rhyno way and found his solution worked fine. – QMaster Oct 20 '14 at 08:58

Rhyno van der Sluijs · Answer 2 · 2013-02-10T23:31:08.637

4

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

edited Feb 10 '13 at 23:31

answered Feb 08 '13 at 05:43

Rhyno van der Sluijs

41
2

+1 Awesome man. I tried some solution but some of them can't work or bad performance on big length or small length. You corrected the accepted answer and your way is working. this is other one of other solution got accept sign but useless. http://stackoverflow.com/questions/10852612/split-string-in-sql-server-to-a-maximum-length-returning-each-as-a-row and i want write an answer for that based of your solution if you don't mind. Thanks again. – QMaster Oct 20 '14 at 09:01

TSQL UDF To Split String Every 8 Characters

2 Answers2

Linked