HostWeb Forums » Microsoft Databases » microsoft.public.sqlserver.programming » Insert characters every ### characters
Topic: Insert characters every ### characters
I have a field in my database say "metadata" and I need to enter "|>"
without the quotes into each value in the metadata column every 2000
characters. So if there are 5000 characters it would put in |> twice
and if it was 2001 characters it would put one |> in right before the
last character. Also some of this data is so lengthy I needed to put
it into an NTEXT field. If this can be done with standard varchar but
not with NText I can try and figure out what to do about that. Any
suggestions?
Thanks.
JR
Replies below ↓
Replies
Re: Insert characters every ### characters
Also using SQL Server 2000.
Thanks
JR
Re: Insert characters every ### characters
By the way, I have been able to put this into a varchar field of 8000
characters now so if that makes things easier let me know. Not sure
if there is a way to RegX this or some way to do some kind of divisor
where if the current character's position divided by say 400 or
something has no remainder stick in a "|>". Just a thought.
Thanks.
JR
Re: Insert characters every ### characters
On Jun 10, 9:04=A0am, JR <jrik...@yahoo.com> wrote:
> By the way, I have been able to put this into a varchar field of 8000
> characters now so if that makes things easier let me know. =A0Not sure
> if there is a way to RegX this or some way to do some kind of divisor
> where if the current character's position divided by say 400 or
> something has no remainder stick in a "|>". =A0Just a thought.
>
> Thanks.
>
> JR
I wouldn't bother being clever about it; just use a loop. It's not
going to be spending very long in there.
drop function InsertDelim
go
CREATE FUNCTION InsertDelim(@source varchar(8000), @delim varchar(10),
@span int)
RETURNS varchar(8000)
AS
BEGIN
DECLARE @i integer
DECLARE @retval varchar(8000)
select @i =3D 1, @retval=3D''
WHILE @i < 8000/@span
BEGIN
SET @retval =3D @retval + SUBSTRING(@source, 1+(@i-1)*@span, @span)
IF len(@source) < @i * @span
BREAK
ELSE
SELECT @retval =3D @retval + @delim, @i=3D@i+1
END
RETURN @retval
END
go
select dbo.InsertDelim(replicate('ABCDEFGH',30), '|>', 24)
result:
ABCDE|>FGHAB|>CDEFG|>HABCD|>EFGH
Re: Insert characters every ### characters
On Jun 10, 9:05=A0am, rpresser <rpres...@gmail.com> wrote:
> On Jun 10, 9:04=A0am, JR <jrik...@yahoo.com> wrote:
>
> > By the way, I have been able to put this into a varchar field of 8000
> > characters now so if that makes things easier let me know. =A0Not sure
> > if there is a way to RegX this or some way to do some kind of divisor
> > where if the current character's position divided by say 400 or
> > something has no remainder stick in a "|>". =A0Just a thought.
>
> > Thanks.
>
> > JR
>
> I wouldn't bother being clever about it; just use a loop. It's not
> going to be spending very long in there.
>
> drop function InsertDelim
> go
> CREATE FUNCTION InsertDelim(@source varchar(8000), @delim varchar(10),
> @span int)
> RETURNS varchar(8000)
> AS
> BEGIN
> =A0 DECLARE @i integer
> =A0 DECLARE @retval varchar(8000)
> =A0 select @i =3D 1, @retval=3D''
> =A0 WHILE @i < 8000/@span
> =A0 BEGIN
> =A0 =A0 =A0SET @retval =3D @retval + SUBSTRING(@source, 1+(@i-1)*@span, @s=
pan)
> =A0 =A0 =A0IF len(@source) < @i * @span
> =A0 =A0 =A0 =A0 BREAK
> =A0 =A0 =A0ELSE
> =A0 =A0 =A0 =A0 SELECT @retval =3D @retval + @delim, @i=3D@i+1
> =A0 END
> =A0 RETURN @retval
> END
> go
> select dbo.InsertDelim(replicate('ABCDEFGH',30), '|>', 24)
>
> result:
> ABCDE|>FGHAB|>CDEFG|>HABCD|>EFGH
Thanks for the reply. I'll give this a spin as soon as I can figure
out how to convert my ntext to varchar(8000). When I try and change
it from NTEXT to VARCHAR and ignore the warnings, I get:
'TEST' table
- Unable to modify table.
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot
create a row of size 8405 which is greater than the allowable maximum
of 8060.
[Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been
terminated.
Is this a SQL Limitation on the amount of data in a given "row" or it
complaining about truncated data?
Thanks.
JR