Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

"function" Expand / Collapse
Author
Message
Posted Wednesday, October 17, 2007 5:17 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, October 27, 2009 3:32 AM
Points: 21, Visits: 55
Let the value be ‘1, 2, 3, 4……100’. How to write a function to split the value and store the numbers in the table variable. can anyone help me to solve this
Post #411654
Posted Wednesday, October 17, 2007 5:25 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, January 26, 2012 5:26 AM
Points: 1,367, Visits: 1,585
CREATE FUNCTION dbo.split ( @in NVARCHAR(4000) )
RETURNS @result TABLE
( seqNr INT IDENTITY(1, 1)
, item NVARCHAR(100)
)
AS BEGIN
DECLARE @i INT
SET @i = 1

WHILE ( CHARINDEX(',', @in) > 0 )
BEGIN
INSERT INTO @result ( item )
SELECT LTRIM(RTRIM(SUBSTRING(@in, 1, CHARINDEX(',', @in) - 1)))
SET @in = SUBSTRING(@in, CHARINDEX(',', @in) + 1, LEN(@in))
SET @i = @i + 1
END

INSERT INTO @result ( item )
SELECT LTRIM(RTRIM(@in))

RETURN
END


Regards,
Andras




Andras Belokosztolszki, MCPD, PhD
GoldenGate Software
Post #411661
Posted Wednesday, October 17, 2007 5:34 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, February 24, 2010 4:10 AM
Points: 1,553, Visits: 2,232
Hi,
There are a few postings and articles for delimiting strings into tables.

here is one that I saw the last time this someone brough this topic.

hope it helps
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=76033


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


Posting Best Practices
Numbers / Tally Tables

SQL-4-Life
Post #411667
Posted Wednesday, October 17, 2007 6:58 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, April 08, 2014 6:13 AM
Points: 1,694, Visits: 19,550
Using a table of numbers as in

http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-numbers-table.html

DECLARE @str VARCHAR(100)
SET @str='1, 2, 3, 4, 5, 50, 99 ,100'


SELECT CAST(SUBSTRING(@str,
Number,
CHARINDEX(',',
@str+',',
Number)-Number) AS INT) AS Val
FROM dbo.Numbers
WHERE Number BETWEEN 1 AND LEN(@str)+1
AND SUBSTRING(','+@str,Number,1)=','


This can also be easily be done with a CLR (using a the C# 'Split' function)




____________________________________________________

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

Never approach a goat from the front, a horse from the rear, or a fool from any direction.
Post #411696
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse