I have a string ,want to split the values after every space as column value and insert them into a table
1306453 0 0 0 0 0
col1 col2 col3 col4 col5 col6
1306453 0 0 0 0 0
Technology Tips and News
I have a string ,want to split the values after every space as column value and insert them into a table
1306453 0 0 0 0 0
col1 col2 col3 col4 col5 col6
1306453 0 0 0 0 0
The function at
http://www.sommarskog.se/arrays-in-sql-2005.html#iter-list-of-integers
does exactly this.
Hi Sufian,
Try this
DECLARE @STRSQL varchar(1000)Mohammad,
You can create a function similar to the following and then call it from a tsql statement inserting the returned tabular values into the table.
-- CREATE FUNCTION CREATE FUNCTION [dbo].[fn_UtilSplit] ( @Text ntext, @Delimiter varchar(20) = ' ' ) RETURNS @tResult TABLE ( [Index] smallint NOT NULL, Value nvarchar(4000)/* NOT NULL,*/ PRIMARY KEY ( [Index] ) ) AS BEGIN DECLARE @bContinue bit, @chValue nvarchar(4000), @iIndex smallint, @iLenDelimiter smallint, @iLenText smallint, @iEndPos smallint, @iStartPos smallint SET @iLenText = DATALENGTH(@Text) SET @iLenDelimiter = DATALENGTH(@Delimiter) IF (@iLenDelimiter = 0) BEGIN SET @iIndex = 0 SET @iStartPos = 1 WHILE (@iStartPos <= @iLenText) BEGIN SET @chValue = SUBSTRING(@Text, @iStartPos, 1) INSERT INTO @tResult ( [Index], Value ) VALUES ( @iIndex, @chValue ) SET @iIndex = @iIndex + 1 SET @iStartPos = @iStartPos + 1 END END ELSE BEGIN SET @bContinue = 1 SET @iIndex = 0 SET @iStartPos = 1 WHILE (@bContinue = 1) BEGIN SET @iEndPos = CHARINDEX(@Delimiter, @Text, @iStartPos) IF (@iEndPos > 0) BEGIN SET @chValue = SUBSTRING(@Text, @iStartPos, (@iEndPos - @iStartPos)) SET @iStartPos = @iEndPos + @iLenDelimiter END ELSE BEGIN SET @chValue = SUBSTRING(@Text, @iStartPos, @iLenText) SET @bContinue = 0 END INSERT INTO @tResult ( [Index], Value ) VALUES ( @iIndex, @chValue ) SET @iIndex = @iIndex + 1 END END RETURN END
INSERT INTO table1(col1, col2, col3, col4, col5, col6) SELECT * FROM ( SELECT [index], value FROM dbo.fn_UtilSplit('1306453 0 0 0 0 0', ' ') ) AS a PIVOT ( MAX(value) FOR [index] IN ([0],[1],[2],[3],[4],[5]) )AS p
HI Milan,
this is what i have been trying with ur code
SELECT REPLACE(_txtrow,' ',''' as Col,''') + ''''+'Col' from #_txtrow
and this is result i got
' as Col,'1306453' as Col,'0' as Col,'0' as Col,'NaN' as Col,'0' as Col,'0'Col
CREATE FUNCTION iter$simple_intlist_to_tbl (@list nvarchar(MAX)) RETURNS @tbl TABLE (number int NOT NULL,pos int NOT NULL) AS BEGIN DECLARE @pos int, @nextpos int, @valuelen int, @seq int; SELECT @pos = 0, @nextpos = 1,@seq=1 WHILE @nextpos > 0 BEGIN SELECT @nextpos = charindex(' ', @list, @pos + 1) SELECT @valuelen = CASE WHEN @nextpos > 0 THEN @nextpos ELSE len(@list) + 1 END - @pos - 1 INSERT @tbl (number,pos) VALUES (convert(int, substring(@list, @pos + 1, @valuelen)),@seq) SELECT @pos = @nextpos,@seq=@seq+1 END RETURN END GO DECLARE @T TABLE(STRING VARCHAR(99)) INSERT INTO @T VALUES('1306453 0 0 0 0 0') SELECT * FROM @T CROSS APPLY iter$simple_intlist_to_tbl(LTRIM(RTRIM(STRING))) c PIVOT ( MAX(c.number) FOR POS IN([1],[2],[3],[4],[5],[6]) ) P