Problem with WITH Statement, trying to get same

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Problem with WITH Statement, trying to get same

Author

Topic

Planet_x
Starting Member

15 Posts

Posted - 2010-09-06 : 13:12:01

I am trying to change the following script using a WITH statement that is supposed to split up a string based on length I specify (size) and position of spaces. I changed the with to just declare the table instead however the output I get is not the same, the original code is -


DECLARE     @String VARCHAR(MAX), 
            @Size TINYINT

SELECT      @String = 'Hello my name is Jeff. I need some help on a project because ', 
            @String = @String + 'right now this is how the application i am working ', 
            @String = @String + 'with displays data.', 
            @Size = 45 

;WITH Peso (Part, StartPos, Size) 
AS (
    SELECT  1,
            1,
            CAST(@Size - CHARINDEX(' ', REVERSE(LEFT(SUBSTRING(@String, 1, @Size) + ' ', @Size))) AS INT)

UNION ALL 

    SELECT  Part + 1, 
            StartPos + Size + 1, 
            CAST(@Size - CHARINDEX(' ', REVERSE(LEFT(SUBSTRING(@String, StartPos + Size + 1, @Size) + ' ', @Size))) AS INT) 
    FROM    Peso 
    WHERE   StartPos + Size <= DATALENGTH(@String) 
) 

SELECT      Part, 
            SUBSTRING(@String, StartPos, Size) 
FROM        Peso 
ORDER BY    Part

Which outputs - Part
---- --------------------------------
1 Hello my name is Jeff. I need
2 some help on a project because
3 right now this is how the
4 application i am working with
5 displays data.

And I have tried to modify my code just to create the table and do the same thing like so -


DECLARE     @String VARCHAR(200)
DECLARE @Size int

SELECT      @String = 'Hello my name is Jeff. I need some help on a project because ', 
            @String = @String + 'right now this is how the application i am working ', 
            @String = @String + 'with displays data.', 
            @Size = 45

DECLARE @Peso TABLE (Part int, StartPos int, Size int)

INSERT INTO @Peso
    SELECT  1,
            1,
            CAST(@Size - CHARINDEX(' ', REVERSE(LEFT(SUBSTRING(@String, 1, @Size) + ' ', @Size))) AS INT)

UNION ALL 

    SELECT  Part + 1, 
            StartPos + Size + 1, 
            CAST(@Size - CHARINDEX(' ', REVERSE(LEFT(SUBSTRING(@String, StartPos + Size + 1, @Size) + ' ', @Size))) AS INT) 
    FROM    @Peso 
    WHERE   StartPos + Size <= DATALENGTH(@String) 


SELECT      Part, 
            SUBSTRING(@String, StartPos, Size) 
FROM        @Peso 
ORDER BY    Part

Except now my output is -
1 Hello my name is Jeff. I need some help on a
2 project because right now this is how the

and I lose the rest of the lines. Any help on how to change this like the first one but in SQL 2000 so I get all the lines greatly appreciated!

MSquared
Yak Posting Veteran

52 Posts

Posted - 2010-09-15 : 10:17:15

I think you will need a while loop in 2000

DECLARE @String VARCHAR(200)
DECLARE @Size int
Declare @Length int
Declare @StartPos int
Declare @Part int
Declare @PrevLength int

SELECT @String = 'Hello my name is Jeff. I need some help on a project because ',
@String = @String + 'right now this is how the application i am working ',
@String = @String + 'with displays data.',
@Size = 30

DECLARE @Peso TABLE (Part int, StartPos int, Size int)

set @Length = CHARINDEX(' ', REVERSE(LEFT(SUBSTRING(@String, 1, @Size) + ' ', @Size)))
set @StartPos = 1
set @Part = 1

while @Length > 0
begin
INSERT INTO @Peso
SELECT @Part,
@StartPos,
CAST(@Size - CHARINDEX(' ', REVERSE(LEFT(SUBSTRING(@String, @StartPos, @Size) , @Size))) AS INT)

set @StartPos = @StartPos + CAST(@Size - CHARINDEX(' ', REVERSE(LEFT(SUBSTRING(@String, @StartPos, @Size) , @Size))) AS INT) + 1
set @PrevLength = @Length
set @Length = CHARINDEX(' ', REVERSE(LEFT(SUBSTRING(@String, @StartPos, @Size) , @Size)))
set @Part = @Part + 1
end

SELECT Part,
SUBSTRING(@String, StartPos, Size)
FROM @Peso
ORDER BY Part

For Faster results please follow the posting guidelines here

http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Planet_x
Starting Member

15 Posts

Posted - 2010-09-16 : 10:11:07

Thanks, that worked.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources