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?
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With