"function"
-
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
-
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 -
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:w00t:
SQL-4-Life
Posting Best Practices[/url]
Numbers / Tally Tables[/url] -
Using a table of numbers as in
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)
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply