Problem with the text data column, variable

  • HBhagat

    Hall of Fame

    Points: 3284

    Hi,

    I have a problem with my procedure.

    The work is I will be reciving a comma separeted text value in a variable and i have segregate the comma seperareted .

    So i had made the procedure. The procedure does that but the moment it reaches the 1000th row problem occurs and it just take the first 2 characters and replace it with the rest of the values after 1000 rows.

    Please help.

    I am not able to debgug as to where the problem is ?:

    CREATE PROCEDURE proc_Function_Explode

    (@Delimiter VARCHAR(10),

    @Value TEXT )

    AS

    BEGIN

    DECLARE @temp_Value INT ,

    @TotalLength INT ,

    @T_Length INT

    SELECT @TotalLength = ISNULL(DATALENGTH(@Value),'')

    IF @TotalLength = ''

    BEGIN

    RETURN

    END

    -- Table to store the values after seggregating the values from the text field.

    CREATE TABLE #Temp

    (Item_Id INT NOT NULL)

    -- Temp table to store the Total values for further manipulations for the text field.

    CREATE TABLE #test

    (Next_Expr text)

    -- Taking all the data into the temporary table for all the

    INSERT INTO #test

    VALUES (@Value)

    IF (@Delimiter = ',') -- Begin for delimiter ',' for comma seperated.

    BEGIN

    -- Populating the first value from the text variable .

    SELECT @temp_Value = CONVERT(INT , (CONVERT(VARCHAR(100),(SUBSTRING(@Value , 1, (PATINDEX('%,%' ,@Value)-1))))))

    --Populating the variable with the lenght of the variables.

    SELECT @T_Length = LEN(@temp_Value)

    -- Begin of the While Loop

    -- While loop run so that it populates one by one value in the table.

    WHILE (@TotalLength - @T_Length > 0)

    BEGIN

    -- Inserting the value in the #temp table from the variable.

    INSERT INTO #Temp

    SELECT @temp_Value

    -- Updating the table with the next set of values to be extracted.

    UPDATE #test

    SET Next_Expr = SUBSTRING(Next_Expr, (PATINDEX('%,%' ,Next_Expr))+1,@TotalLength-(len(@temp_Value)+1))

    --select * from #test

    -- Taking into the variable the values into the temp variable.

    SELECT @temp_Value = CASE WHEN (PATINDEX('%,%' ,Next_Expr) <> 0)

    THEN CONVERT(INT , (CONVERT(VARCHAR(100),(SUBSTRING(Next_Expr , 1, (PATINDEX('%,%' ,Next_Expr)-1))))))

    ELSE CONVERT(int ,(CONVERT(VARCHAR(100),Next_Expr )))

    END

    FROM #test

    -- IF (@temp_Value = 12)

    -- BEGIN

    -- SELECT @temp_Value ,@T_Length

    -- Select * from #test

    -- select * from #Temp

    --

    -- break

    -- END

    -- Calculation for the while loop to run.

    SELECT @T_Length = @T_Length+Len(@temp_Value)+1

    END -- End of while loop.

    SELECT * FROM #Temp

    END -- End of If.

    END

    go

    for testing the parameters are

    EXEC proc_function_explode ',', '1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567'

    please help...

  • Jack Corbett

    SSC Guru

    Points: 184381

    Check out this article, http://www.sqlservercentral.com/articles/TSQL/62867/. It contains a great method for parsing a string in the Stepping through Characters section about halfway down.

    Jack Corbett
    Consultant - Straight Path Solutions
    Check out these links on how to get faster and more accurate answers:
    Forum Etiquette: How to post data/code on a forum to get the best help
    Need an Answer? Actually, No ... You Need a Question

  • HBhagat

    Hall of Fame

    Points: 3284

    The article is good.

    But it did not had the solution of my problem. I have to receive a text field in which you cannot add any extra ',' anywhere .

    I have asked the front end people to send the parameter to add a comma at the last.

    I have the problem that when it is run in text field after certain time or point it just take 2 values from the values and dispalys.

    , which is incorrect.

    eg.

    I have a values '121212,1213231,3123,23,4444,4444,4444,444,4444,444,'

    i want it to be displayed like

    121212

    1213231

    3123

    23

    4444

    4444

    4444

    444

    4444

    444

    but it displays

    121212

    1213231

    3123

    23

    4444

    44

    44

    44

    44

    44

    Now I am in jinx where is the problem in my code , where is it breaking?

  • Jack Corbett

    SSC Guru

    Points: 184381

    harsha.bhagat1 (8/29/2008)


    I have to receive a text field in which you cannot add any extra ',' anywhere .

    If you are receiving the data as a parameter why can't you declare a new variable to add the beginning and ending commas? Like this:

    Declare @text varchar(8000)

    Set @text = ',' + @parameter + ','

    Then you process the @text variable or you can just do this:

    Select

    Substring(',' + @text + ',', N+1, Charindex(',', ',' + @text + ',', N+1) - n -1)

    From

    tally

    Where

    N <= Len(',' + @text + ',') AND

    SUBSTRING(',' + @text + ',', N,1) = ',' And

    Charindex(',', ',' + @text + ',', N+1) > 0

    order by

    n

    Jack Corbett
    Consultant - Straight Path Solutions
    Check out these links on how to get faster and more accurate answers:
    Forum Etiquette: How to post data/code on a forum to get the best help
    Need an Answer? Actually, No ... You Need a Question

  • Ron McCullough

    SSC Guru

    Points: 63877

    This might give you a start, it was posted here on SCC but by whom my memory fails me.

    DECLARE @string VARCHAR(50)--must be sized large enough for given string

    DECLARE @Find AS INT

    DECLARE @Char AS VARCHAR(5)

    DECLARE @First AS INT

    DECLARE @Rep AS VARCHAR(5)

    DECLARE @position AS INT

    -- Initialize the current position and the string variables.

    SET @position = 1

    SET @First = 1

    SET @Rep = ''

    SET @string = '22,4444,444,66,44,4,5,55,5555,55'

    --SET @string = '~~F~T~G'

    WHILE @position <= DATALENGTH(@string)

    BEGIN

    SET @Find = (SELECT ASCII(SUBSTRING(@string, @position, 1)))

    /*--this is the ASCII value for the string separator in this case a comma

    it may be any character */

    IF @Find <> 44 BEGIN

    SET @Char = (SELECT CHAR(ASCII(SUBSTRING(@string, @Position, (@position - @First) +1))))

    SET @Rep = @Rep + @Char

    END

    ELSE

    BEGIN

    PRINT 'Rep found*' + @Rep + '*' --used only during testing

    SET @First = @position

    SET @Rep = ''

    END

    IF @position = DATALENGTH(@string)

    BEGIN

    IF @position - DATALENGTH(@string) > 0

    BEGIN

    SET @Char = (SELECT CHAR(ASCII(SUBSTRING(@string, @Position, (@position - DATALENGTH(@string))) )))

    SET @Rep = @Rep + @Char

    END

    PRINT 'Last Rep found*' + @Rep + '*'--only used during testing

    END

    SET @position = @position + 1

    END

    For the string defined above the result is

    Rep found*22*

    Rep found*4444*

    Rep found*444*

    Rep found*66*

    Rep found*44*

    Rep found*4*

    Rep found*5*

    Rep found*55*

    Rep found*5555*

    Last Rep found*55*

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • Jeff Moden

    SSC Guru

    Points: 996436

    I gotto go with Jack on this one... use a Tally table instead of a While loop. In fact, here's an article on what a Tally table is and how it can be used to replace While loops...

    http://www.sqlservercentral.com/articles/TSQL/62867/

    The last part of that article tells you how to do a "split".

    For much more detail on how to do both simple split's and how to pass arrays as parameters, here's a different article...

    http://www.sqlservercentral.com/articles/T-SQL/63003/

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Forum FAQ

  • HBhagat

    Hall of Fame

    Points: 3284

    My problem still exists.

    I cannot change the the datatype of my variable as there will be a data comming more that 8000.

    If you test the values what i have provided then you can see that its breaking after 1000. I get incorrect resultset after 1000 records.

    I am not able to understand where the code is breaking or where is the error happening.

    Using the tally table can help me with the varchar datatype values but not with the text values.

    If you execute the test script given below you can understand my problem.

    EXEC proc_function_explode ',' , '1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,1234567,'

    The procedure is provieded in the first issue.

    Thanks in advance everybody for the help..

  • Deeptiprasad

    SSC Eights!

    Points: 935

    If you are using sql server 2005, then use VARCHAR(MAX) datatype instead of text datatype.

    if you are using sql server 2000, then you have to first split the string to a variable of length less than 8000. Then split the string again for the specified delimiter.

  • ChrisM@Work

    SSC Guru

    Points: 186093

    Of course you can use a tally table with text values.

    This is ugly because you can't use CHARINDEX() with text values and it assumes that the max length of a substring is 12 characters, but it works...and if something works then it can usually be made to look nicer and work better...

    [font="Courier New"]CREATE PROCEDURE proc_Function_Explode_New

       (@Delimiter CHAR(1),

       @String TEXT)

    AS

    SELECT

       CASE WHEN PATINDEX('%'+@Delimiter+'%', SUBSTRING(@String, number+1, 12)) > 0

           THEN SUBSTRING(@String, CASE number WHEN 1 THEN 1 ELSE number+1 END,

               PATINDEX('%'+@Delimiter+'%', SUBSTRING(@String, CASE number WHEN 1 THEN 1 ELSE number+1 END, 12))-1)

           ELSE SUBSTRING(@String, CASE number WHEN 1 THEN 1 ELSE number+1 END,

               DATALENGTH(SUBSTRING(@String, CASE number WHEN 1 THEN 1 ELSE number+1 END, 12))) END

    FROM Numbers

    WHERE number <= DATALENGTH(@String)

       AND (SUBSTRING(@String, number, 1) = @Delimiter

       OR number = 1)

    ORDER BY number

    RETURN [/font]

    [font="Arial"]“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw[/font]


    For fast, accurate and documented assistance in answering your questions, please read this article[/url].
    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]
    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
    [url

  • ChrisM@Work

    SSC Guru

    Points: 186093

    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

    [font="Arial"]“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw[/font]


    For fast, accurate and documented assistance in answering your questions, please read this article[/url].
    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]
    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
    [url

  • HBhagat

    Hall of Fame

    Points: 3284

    It really works..:w00t:

    Thank you soooooooooooooooooooooooooooo much.. :w00t::w00t::w00t:

    Thank you so much..

    :):laugh:

  • ChrisM@Work

    SSC Guru

    Points: 186093

    Here's another slightly different version which is easier to test and accounts for leading and trailing commas:

    [font="Courier New"]CREATE PROCEDURE proc_Function_Explode_New2

       (@Delimiter CHAR(1),

       @String TEXT)

    AS

    DECLARE @StringLength INT

    SET @StringLength = DATALENGTH(@String)

    CREATE TABLE #Temp (

       [RowID] [int] IDENTITY (1, 1) NOT NULL,

       number INT

    ) ON [PRIMARY]

    -- If the string doesn't start with a delimiter, then put the first element into the table

    IF NOT SUBSTRING(@String, 1, 1) = @Delimiter

       INSERT INTO #Temp (number) VALUES (0)

    INSERT INTO #Temp

    SELECT number

    FROM Numbers

    WHERE number <= DATALENGTH(@String)

       AND SUBSTRING(@String, number, 1) = @Delimiter

    ORDER BY number

    -- If the string doesn't end with a delimiter, then put the last element into the table

    IF NOT SUBSTRING(@String, @StringLength, 1) = @Delimiter

       INSERT INTO #Temp (number) VALUES (@StringLength+1)

    SELECT SUBSTRING(@String, a.number+1, b.number-a.number-1)

    FROM #Temp a

    INNER JOIN #Temp b ON b.RowID = a.RowID + 1

    [/font]

    Cheers

    ChrisM

    [font="Arial"]“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw[/font]


    For fast, accurate and documented assistance in answering your questions, please read this article[/url].
    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]
    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
    [url

  • HBhagat

    Hall of Fame

    Points: 3284

    Thanks a million..

    It works..

    Thank you so much..

    🙂

  • ChrisM@Work

    SSC Guru

    Points: 186093

    harsha.bhagat1 (9/1/2008)


    Thanks a million..

    It works..

    Thank you so much..

    🙂

    You're welcome Harsha. Don't ya just love a happy customer? 😀

    [font="Arial"]“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw[/font]


    For fast, accurate and documented assistance in answering your questions, please read this article[/url].
    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]
    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
    [url

  • HBhagat

    Hall of Fame

    Points: 3284

    Yep,

    But Thank you so much..:)

Viewing 15 posts - 1 through 15 (of 22 total)

You must be logged in to reply to this topic. Login to reply