Geek Answers Handbook

TSQL UDF for line splitting Every 8 characters

Someone decided to collect a bunch of times in one column, so the value of the column may look like this:

08:00 AM01:00 PM

And the other column contains the date in the following format;

 20070906

I want to write UDF to normalize this data in a single SQL query so that 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

Converting to datetime type is very simple ... but I need to divide the time part by every 8 characters to get an individual time.

Does anyone know of an existing UDF for this?

+4
source share
2 answers

Try this, it will split your string into pieces 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 instance:

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

Gives this result:

Result

08:00 AM

01:00 PM

+8
source

In the request above error, the request described below corrects this. In addition, I made the returned table contain a sequence column so that I could determine which sequence the split was 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
+2
source

More articles:


All Articles