AngelEyes on .Net

Thanks, this is very useful.
/Daniel

Thanks, Daniel.

thanks

Thanks a lot

Thanks a lot. Its very useful.

This is great! :)

If I then want to get back only the nth row from the resulting table how would I get it? I would presume there is some sort of rownum function that we can use?

Good f(x)

Hi
I was write this sp :

UPDATE TBLMessages
SET Location = @Location, FolderID = @FolderID
WHERE (dbo.TBLMessages.MsgID in (SELECT item FROM dbo.FnSplit(MsgID,',')))

But It Updates All Records !!!

If I were trying to use this to split a column that I select, how would I do that?

For example, say we have some customer numbers that are set up in a special fashion like 000-xxx-xxx, and others that are set up like normal numbers areacode-prefix-number, and I want to throw that into a case when and pull a list of how many users we have set up like that, kind of like this:

SELECT SUM(CASE WHEN (SELECT item FROM (SELECT ROW_NUMBER() OVER (ORDER BY item) AS RowNum, * FROM fnSplit(SELECT dbo.CUSTOMER.PHONE_NUM FROM dbo.CUSTOMER WHERE CUSTOMER.CUST_ID=1234, '-')) drvTbl WHERE RowNum = 1) THEN 1 ELSE 0 END) AS IRREGULAR_COUNT FROM dbo.CUSTOMER

The fnSplit function doesn't allow me to throw a select statement in it (or maybe I'm just missing something here).

Any ideas?

Yea, I figured that out after banging my head against the wall for an hour or so. I have a tendancy to overthink things sometimes, haha.

Thanks for the reply, and the great blog.

Is there a way to use fnsplit on a column that is selected?

For example:
select * from dbo.fnsplit(select prov_title from provider where prov_title > ' ')

This doesn't work.

Hey Marcie.
To my knowledge, you cannot use the function like that, in a query.
You can use a cursor to do the same thing, but of course you'll have to watch the performance.

Thanks!
It's very useful to me.

Thanks a Lot.

Sure, glad to be of help.

Hello,

how can I use this function with a cursor?

I want to use this function like this example:
select * from dbo.fnsplit(select prov_title from provider where prov_title > ' ')

This tutorial should be put in a place of honor. The best!!!

how can I use this function with a cursor?

Thanks!
It's very useful to me.

Thanks for writing.but please letters be more big.:)

Thanks you

thanks you

Thank you very useful information.

thanks a lot..

Very thanks.

Very nice, thanks.....

Hı :) Thank you post...

SMF Destek

Thanks for articles.

Sorry my english firstly, i read well but i cant write :), So its useful post for me and i bookmarked your blog.

wordpress themes

web tasarım

I'll change the fonts, or the whole theme :-)

Thank you for information.

useful article, many thanks.


Daily Linux News
web hosting

i have modified to return item at specified index:

ALTER FUNCTION [dbo].[split](@sInputList VARCHAR(8000),@sDelimiter VARCHAR(8000) = ',',@Index INT)
RETURNS NVARCHAR(4000)
BEGIN
DECLARE @sItem VARCHAR(8000)
DECLARE @retval NVARCHAR(4000)
DECLARE @tempIndex INT
DECLARE @List TABLE(item VARCHAR(8000))
SET @tempIndex = 0
WHILE Charindex(@sDelimiter,@sInputList,0) <> 0
BEGIN
SELECT @sItem = Rtrim(Ltrim(Substring(@sInputList,1,Charindex(@sDelimiter,@sInputList,0) - 1))),
@sInputList = Rtrim(Ltrim(Substring(@sInputList,Charindex(@sDelimiter,@sInputList,0) + Len(@sDelimiter),
Len(@sInputList))))
IF Len(@sItem) > 0
INSERT INTO @List
SELECT @sItem
IF (@tempIndex = @Index)
SET @retval = @sItem
SET @tempIndex = @tempIndex + 1
END
IF Len(@sInputList) > 0
INSERT INTO @List
SELECT @sInputList -- Put the last item in
IF (@tempIndex = @Index)
SET @retval = @sItem
RETURN @retval
END
GO

Way to go, Fransisco, Thanks for letting us know about it!

This doesn't work if space is your actual separator. ie there should be a NULL item, ala the first test, in the third test. As written extra spaces are discarded.

By "this" I was referring to the original post.

Here is a fix. Why in the world MS thought it smart to not count a space in the len() function is beyond me.

drop function dbo.fnSplit

go

CREATE FUNCTION dbo.fnSplit(
@sInputList VARCHAR(8000) -- List of delimited items
, @sDelimiter VARCHAR(8000) = ',' -- delimiter that separates items
) RETURNS @List TABLE (item VARCHAR(8000))

BEGIN
DECLARE @sItem VARCHAR(8000)
WHILE CHARINDEX(@sDelimiter,@sInputList,0) <> 0
BEGIN
SELECT
@sItem=SUBSTRING(@sInputList,1,CHARINDEX(@sDelimiter,@sInputList,0)-1),
@sInputList=SUBSTRING(@sInputList,CHARINDEX(@sDelimiter,@sInputList,0)+DATALENGTH(@sDelimiter),DATALENGTH(@sInputList))

IF LEN(@sItem) > 0
INSERT INTO @List SELECT @sItem
ELSE IF LEN(@sItem) = 0
INSERT INTO @List SELECT NULL
END

IF LEN(@sInputList) > 0
INSERT INTO @List SELECT @sInputList -- Put the last item in
RETURN
END
GO
--Test
select * from fnSplit('1,22,333,444,,5555,666', ',')
select * from fnSplit('1##22#333##444','##') --note second item has embedded #
select * from fnSplit('1 22 333 444 5555 666', ' ')

Thanks ,for articles.

Thanks for...

Thanks a lot.. ty.

Thank you

mersi şekerim

Nice work. Was just about to write this myself then thought I should see if somebody else had already posted a solution before I reinvented the wheel. Now that's the proper meaning of resuable code.

thanks admins very nicess

thanks you goods

Good on your

THanks a lot

Lot name a

Thanks for the reply, and the great blog.

Thanks for the reply, and the great blog.

Good on your

very useful command.thanks for that

THanks a lot