Error when Split function is executed..?

Question

Viewing 3 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Christopher Stobbs SSC-Insane Points: 21098 More actions · Answer 1

Hi,

The reason for this is that you have created a tabular function (meaning it returns a table).

To get data from this type of function you need to treat it as a table when calling it for example:

select * FROM dbo.split('abcdef,ghi',',')

Thanks

Chris

----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley

:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]

SQL-4-Life

Christopher Stobbs SSC-Insane Points: 21098 More actions · Answer 2

Hi ,

Just in case you are interested.

If you have a numbers/tally table in your database which is always good to have.

Here is an alternate to your split function that does not require a loop.

CREATE FUNCTION dbo.Split

(

@ItemList NVARCHAR(4000),

@delimiter CHAR(1)

)

RETURNS @IDTable TABLE (Item VARCHAR(50))

AS

BEGIN

INSERT INTO @IDTable

SELECT SUBSTRING(@ItemList+@delimiter, N,

CHARINDEX(',', @ItemList+@delimiter, N) - N)

FROM dbo.Tally

WHERE N <= LEN(@ItemList)

AND SUBSTRING(@delimiter + @ItemList,

N, 1) = @delimiter

ORDER BY N

RETURN

END

Thanks

Chris

----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley

:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]

SQL-4-Life

sasika99 Old Hand Points: 302 More actions · Answer 3

Hi Chris,

Thank you for the assistance..:)

Problem Solved