string manipulation - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

string manipulation

Author

Topic

waterduck
Aged Yak Warrior

982 Posts

Posted - 2010-02-23 : 03:26:08

how to insert '\n' after every 50 character?

for example
SELECT substring(col1,0,50)+'\n'+substring(col1,51,100)+'\n' FROM table

output
1234...50\n51...100\n

Hope can help...but advise to wait pros with confirmation...

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-02-23 : 03:37:16

Do you want to insert a new line character or just static value '\n'

If you want to insert a new line character , use Char(10)

To insert a static value, use Stuff function

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-02-23 : 03:41:20

quote:
Originally posted by waterduck

how to insert '\n' after every 50 character?

for example
SELECT substring(col1,0,50)+'\n'+substring(col1,51,100)+'\n' FROM table

output
1234...50\n51...100\n

Hope can help...but advise to wait pros with confirmation...

Why do you want to do this?

Madhivanan

Failing to plan is Planning to fail

waterduck
Aged Yak Warrior

982 Posts

Posted - 2010-02-23 : 03:51:48

erm.....
lets say i got varchar(5000), if i non stop typing then the value will be too long to show out at front end...therefore i would like to add '\n' to prevent too long

Hope can help...but advise to wait pros with confirmation...

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-02-23 : 03:54:52

Not beauty...

declare @test varchar(max)
declare @test2 varchar(max)
declare @i int
declare @x int
set @x=1
set @test2=''
select @test=replicate('X',255)
select @test
select @i=len(@test)/50
select @i

while @i > @x
begin
set @test2 = @test2 + substring(@test,@x,50)+'\n'
set @x = @x+1
select left(reverse(@test2),2)
end
if (left(reverse(@test2),2) <> 'n\')
begin
set @test2=@test2 + '\n'
end
select @test2

No, you're never too old to Yak'n'Roll if you're too young to die.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-02-23 : 04:12:48

quote:
Originally posted by waterduck

erm.....
lets say i got varchar(5000), if i non stop typing then the value will be too long to show out at front end...therefore i would like to add '\n' to prevent too long

Hope can help...but advise to wait pros with confirmation...

In fornt end you need to use wordwrap property (or Multiple)

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-23 : 10:08:07

yeah..its a strange thing to change sql to meet this requirement. you should be doing this at your front end to show output in separate lines as Madhi suggested

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources