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)
DECLARE @str as varchar(100)
SET @str='1306453 0 0 0 0 0'
SET @STRSQL = 'SELECT ''' + REPLACE(@STR,' ',''' as Col,''') + ''''+'Col'
EXEC(@STRSQL)

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

Hi Mohammad,

The approach from Milan is a trick for a given string, you can't apply it to a column of a table, for the spaces contained in the column may vary. To reach your goal, you can rely on the functions mentioned in Erland's link. If you are in a hurry, please see below function tweaked from that link .

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
 

If you have any feedback on our support, you can click here.