would someone else try this?

[Dan Holmes]

I am getting an error i don't understand. I would like for someone else to try and repeat it if possible. I am on
Sql2005sp2.

The goal is to take a string and split it based on a delimiter. The following is the split function. You may recognize
the form. I found it from this NG but i don't remember where. tblCalendar is my source of numbers so don't let that
throw you.

CREATE FUNCTION [dbo].[split](@text VARCHAR(MAX), @delimiter VARCHAR(20) = ' ')
RETURNS TABLE
AS
RETURN
(
SELECT ROW_NUMBER() OVER (ORDER BY Id)-1 idx,
SUBSTRING(@text, Id, CAST(CHARINDEX(@delimiter, @text + @delimiter, Id) - CAST(Id AS BIGINT) AS INT)) value
FROM tblCalendar
WHERE Id <= CONVERT(INT, LEN(@text)) AND SUBSTRING(@delimiter + @text, Id, 1) = @delimiter
)

This exercises the above function:
SELECT * FROM dbo.split(
'341|07/25/2008 12:00:00 AM|T,T|752811,752821|07/25/2008 09:00:00 AM,07/25/2008 01:00:00 PM|07/25/2008 09:30:00
AM,07/25/2008 01:30:00 PM|0,0|,,|1'
, '|')

The following causes this error:
Msg 245, Level 16, State 1, Line 11
Conversion failed when converting the varchar value 'æ
ˆrž]Q...........' to data type int.


declare @VehicleID INT
DECLARE vehicle_field_cursor CURSOR
FOR
SELECT Value
FROM dbo.split(
'341|07/25/2008 12:00:00 AM|T,T|752811,752821|07/25/2008 09:00:00 AM,07/25/2008 01:00:00 PM|07/25/2008 09:30:00
AM,07/25/2008 01:30:00 PM|0,0|,,|1'
, '|')
OPEN vehicle_field_cursor
FETCH NEXT FROM vehicle_field_cursor INTO @VehicleID
print @VehicleID
CLOSE vehicle_field_cursor
Deallocate vehicle_field_cursor

I have another version of split that is very procedural that has the same signature as this one and it runs quite well.
I have had to revert to it in my code because of this error.

I would greatly appreciate it if someone would take the time to see if this error happens for them as well. And if
possible find some explanation and/or remedy.

thanks

dan

 

[Jon Skeet [C# MVP]]

I am getting an error i don't understand.  I would like for someone else to try and repeat it if possible.  I am on
Sql2005sp2.

Could I suggest you ask on a SQL group? You're more likely to get
accurate responses there than here (a C# group).

Jon

 

[Dan Holmes]

Could I suggest you ask on a SQL group? You're more likely to get
accurate responses there than here (a C# group).

Jon

You are very right. I thought i was posting there. I wasn't paying attention.

 

[Nicholas Paldino [.NET/C# MVP]]

Dan,

Honestly, you don't want to do this in T-SQL. You are better off
creating a managed stored procedure or a managed table-based function which
will take the string, split it, and then return the results to you. In this
case, where you are doing computational work vs. set-based work, the CLR in
SQL Server is almost always the better option.