May 19, 2007 at 5:55 pm
I'm wondering if there's a way to use the string function replicate() to produce an output greater than 8000 bytes. From the documentation REPLICATE returns a varchar of max 8000 but I'm told there's a way to stop this restriction.
My mentor has been giving me tasks to try and accomplish and for this one I'm to create a stored procedure with no limitations, however I'm stuck at this replicate() problem.
Any help or guidance would be great.
Thanks
May 19, 2007 at 9:02 pm
There are a few ways I can think of to return more than 8000 characters to the application, but what exactly do you need to do?
May 19, 2007 at 11:20 pm
thanks for the reply mate,
basically I have to create a stored procedure which will accept several params to produce an output. The only issue I'm having is the replication restriction.
One of the params is say the pattern's width is an integer. If the call passes something greater than 8000 the replicate fn i'm using will simply truncate it. I dont' want this. I thought about creating a UDFn and running a loop but I don't know if there's a 'print' command to not append the endline to the end of the string.
let me know if I need to be more concise. Thanks.
May 20, 2007 at 5:26 am
It's still not at all clear to me what you are trying to do! Can you post in details what problem you are trying to solve?
May 22, 2007 at 4:49 am
hi mate, here's the script. Works fine, with the limitation mentioned above.
drop procedure usp_Display_Pattern
go
create procedure usp_Display_Pattern
@widthOfPattern int,
@hangRight int,
@repeat int,
@startCharacter char,
@hangingIndent int
AS
-- (0) error checking, (0) set some variables
if @widthOfPattern < 0
set @widthOfPattern = 0
if @hangRight < 0
set @hangRight = 0
if @repeat < 0
set @repeat = 0
if @hangingIndent < 0
set @hangingIndent = 0
declare @incVar int,
@incPad int,
@times int
set @times = @repeat
set @incPad = 0
-- (1) character width, (2) how far to the right, (3) repeat times, (4) our character, (5) starting indent
while @times 0
begin
print space(@hangingIndent) + replicate(@startCharacter, @widthOfPattern)
set @incVar = 0
set @incPad = 0
while @incVar < @hangRight*2-1
begin
if @incVar 0 and @hangRight > 0
print space(@hangingIndent) + replicate(@startCharacter, @widthOfPattern)
go
exec usp_Display_Pattern
10, -- 1. Select the width of the pattern
10, -- 2. How far the pattern goes to the right
3, -- 3. How many times the pattern is repeated
'&', -- 4. The character that makes up the pattern and the indent the pattern should start.
10 -- 5. The starting indent
May 22, 2007 at 5:14 am
Replicate by itself will never go over 8000. The only work around is to use replicate more than once in the script. I think it would be the simplest solution in your case!
May 23, 2007 at 8:28 am
Probably easiest is to do something like this (handles up to 24000, assuming PRINT can handle that):
print space(@hangingIndent) + replicate(@startCharacter, CASE WHEN @widthOfPattern > 8000 THEN 8000 ELSE @widthOfPattern END) + replicate(@startCharacter, CASE WHEN @widthOfPattern > 16000 THEN 8000 WHEN @widthOfPattern > 8000 THEN @widthOfPattern - 8000 ELSE 0 END) +
replicate(@startCharacter, CASE WHEN @widthOfPattern > 16000 THEN @widthOfPattern - 16000 ELSE 0 END)
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply