Home Forums SQL Server 7,2000 T-SQL Problem with the text data column, variable RE: Problem with the text data column, variable

  • 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

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    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