Its not the solution..
There is the problem what i found after working on it more today.
I am not able to split my text field. Today I found that the substring is not wrking properly for which my data is not comming properly..
Since there was a suggestion and I think it was you , but it isnot working with the tally table..
Or I think may be the code is not full..
I am still working on it. ..
On the site I found handling text field. So iread and posted my problem
The other one is I had asked as I was notr aware that there was an article over here regarding the text handling..
Sorry if I had given any trouble..
SUBSTRING works with the text datatype provided that the returned part of the string doesn't exceed VARCHAR(8000). The trick is to return manageable chunks from the text data.
Do you have a tally table? Here's code to generate it...
[font="Courier New"]CREATE PROCEDURE [dbo].[MakeNumbersTable]
-- Courtesy of Jeff Moden, SSC
AS
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[Numbers]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE dbo.Numbers
--===== Create and populate the Tally table on the fly
SELECT TOP 1000000
IDENTITY(INT,1,1) AS number
INTO dbo.Numbers
FROM MASTER.dbo.syscolumns sc1,
MASTER.dbo.syscolumns sc2
--===== Add a Primary Key to maximize performance
ALTER TABLE dbo.Numbers
ADD CONSTRAINT PK_numbers_number PRIMARY KEY CLUSTERED (number)
--===== Allow the general public to use it
GRANT SELECT ON dbo.Numbers TO PUBLIC
GO
[/font]
My code works with the test data you posted - note that the test data has adjacent commas in several places.
Cheers
ChrisM
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden