Prev: retrieving connection string w/ ConfigurationManager
Next: Update table in SQL replacing the existing value
From: Hemant on 20 May 2010 09:05
Hi,

I want to insert result of readtext into a temp table.
I am working on crystal report . Crystal report text objtect has a text
limit thats why I want to split data stored in db in multiple rows.
the column filed is text type .
Please help me.

Thanks,
Hemant


From: John Bell on 20 May 2010 12:00
On Thu, 20 May 2010 18:35:34 +0530, "Hemant" <Hemant(a)nomail.com>
wrote:

>Hi,
>
>I want to insert result of readtext into a temp table.
>I am working on crystal report . Crystal report text objtect has a text
>limit thats why I want to split data stored in db in multiple rows.
>the column filed is text type .
>Please help me.
>
>Thanks,
>Hemant
>
Hemant

I assume that if you have a limit to the text size in your report then
you want to split the text column into parts. You could use SUBSTRING
to do this.

e.g.

USE tempdb
GO

CREATE TABLE txttbl ( id int not null identity, textdata text )

INSERT INTO txttbl ( textdata ) SELECT REPLICATE('ABC',8000)
GO


SELECT ID
, SUBSTRING(textdata,1,250) AS str1
, SUBSTRING(textdata,251,250) AS str2
, SUBSTRING(textdata,501,250) AS str3
FROM txttbl

John
From: Hemant on 21 May 2010 01:53
Thanks John

this give data in columns i need data in rows.
i have done this but know i want to pass text column to below sp what to do
this?

Create PRocedure SplitTextToString
(

@myString text,
@CharLimit int
)
AS
BEGIN
Declare
@ReturnTable TABLE
(
-- Add the column definitions for the TABLE variable here
[id] [int] IDENTITY(1,1) NOT NULL,
[part] text NULL
)
if(@CharLimit > 4000)
set @CharLimit = 3999

declare @StrLen int
select @StrLen = Datalength(@myString)
if(@StrLen > @CharLimit)
begin
declare @IsExit bit
declare @Substr nvarchar(4000)
declare @StartPoint int
declare @EndPoint int
declare @strPass int
set @StartPoint = 1
set @EndPoint = @CharLimit
set @IsExit = 0
set @strPass = 0
while (@IsExit = 0)
begin
select @Substr = substring(@myString,@StartPoint,@EndPoint)
insert into @ReturnTable
select @Substr
set @StartPoint =@StartPoint+(a)EndPoint

set @strPass = @strPass + @CharLimit
if(Datalength(@myString)-@strPass < @CharLimit)
begin
set @EndPoint = @EndPoint+ Datalength(@myString)
set @IsExit = 1
end
else
begin
set @EndPoint = @CharLimit
end
end

begin
insert into @ReturnTable
select substring(@myString,@StartPoint,@EndPoint)
end
end
else
begin
insert into @ReturnTable
select @myString
end

select * from @ReturnTable
END

thaks,
Hemant
"John Bell" <jbellnewsposts(a)hotmail.com> wrote in message
news:lgmav5hu4hvu2eqnullvkm023fmq8i3map(a)4ax.com...
> On Thu, 20 May 2010 18:35:34 +0530, "Hemant" <Hemant(a)nomail.com>
> wrote:
>
>>Hi,
>>
>>I want to insert result of readtext into a temp table.
>>I am working on crystal report . Crystal report text objtect has a text
>>limit thats why I want to split data stored in db in multiple rows.
>>the column filed is text type .
>>Please help me.
>>
>>Thanks,
>>Hemant
>>
> Hemant
>
> I assume that if you have a limit to the text size in your report then
> you want to split the text column into parts. You could use SUBSTRING
> to do this.
>
> e.g.
>
> USE tempdb
> GO
>
> CREATE TABLE txttbl ( id int not null identity, textdata text )
>
> INSERT INTO txttbl ( textdata ) SELECT REPLICATE('ABC',8000)
> GO
>
>
> SELECT ID
> , SUBSTRING(textdata,1,250) AS str1
> , SUBSTRING(textdata,251,250) AS str2
> , SUBSTRING(textdata,501,250) AS str3
> FROM txttbl
>
> John


 | 
Pages: 1
Prev: retrieving connection string w/ ConfigurationManager
Next: Update table in SQL replacing the existing value