Need to place in my resultset

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

Transact-SQL (2008)

Need to place in my resultset

Author

Topic

Delinda
Constraint Violating Yak Guru

315 Posts

Posted - 2012-02-08 : 09:37:14

My table and data as following,

declare @t1 table
(remarks varchar(1000))

insert into @t1 values('Bendahari/Setiausaha/Naib Pengerusi/Ketua Seksyen Pasukan/PW II/Staf Sarjan/Sarjan Major/Ketua Trup')
insert into @t1 values('Penolong Bendahari/Penolong Setiausaha/Quartermaster/Penolong Ketua Seksyen Pasukan/Sarjan')
insert into @t1 values('Naib Pengerusi/Setiausaha Pasukan/Rumah Sukan/Rumah Asrama')

I want to place in every 35 character in my remarks. The expected result as following,

Bendahari/Setiausaha/Naib Pengerusi<br>/Ketua Seksyen Pasukan/PW II/Staf S<br>arjan/Sarjan Major/Ketua Trup
Penolong Bendahari/Penolong Setiaus<br>aha/Quartermaster/Penolong Ketua Se<br>ksyen Pasukan/Sarjan
Naib Pengerusi/Setiausaha Pasukan/R<br>umah Sukan/Rumah Asrama

really need help

theboyholty
Posting Yak Master

226 Posts

Posted - 2012-02-08 : 09:45:49

Have a look at the STUFF command

http://msdn.microsoft.com/en-us/library/ms188043.aspx

---------------------------------------------------------------------------------
http://www.mannyroadend.co.uk A Bury FC supporters website and forum

Delinda
Constraint Violating Yak Guru

315 Posts

Posted - 2012-02-08 : 10:01:49

yes sir. but I dont know how to customize the stuff function with my scenario

Delinda
Constraint Violating Yak Guru

315 Posts

Posted - 2012-02-08 : 10:58:56

please help... :(

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-02-08 : 11:18:36

I have tried and searched and found a function that was not working...
I have changed that function to fit the needs.
Here is my solution:

-- create this function once

CREATE FUNCTION StrInsert
  ( @str nvarchar(4000),
    @term nvarchar(10),
    @number int )
RETURNS nvarchar(4000)
AS
BEGIN
  DECLARE @i int, @j int, @stepcount int
  IF (datalength(@str) <= @number) RETURN @str
  SELECT  @i = 1, @j = @number + 1, 
         @stepcount = datalength(@str) / @number
  WHILE @i <= @stepcount
    BEGIN
      SET @str = ISNULL(STUFF(@str, @j, 0, @term), @str)
      SET @j = @j + @number + len(@term)
      SET @i = @i + len(@term)
    END
  RETURN @str
END
go

-- the solution


declare @t1 table
(remarks varchar(1000))

insert into @t1 values('Bendahari/Setiausaha/Naib Pengerusi/Ketua Seksyen Pasukan/PW II/Staf Sarjan/Sarjan Major/Ketua Trup')
insert into @t1 values('Penolong Bendahari/Penolong Setiausaha/Quartermaster/Penolong Ketua Seksyen Pasukan/Sarjan')
insert into @t1 values('Naib Pengerusi/Setiausaha Pasukan/Rumah Sukan/Rumah Asrama')

select
dbo.StrInsert(remarks,'<br>',35)
from @t1

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

X002548
Not Just a Number

15586 Posts

Posted - 2012-02-08 : 12:02:28

SELECT SUBSTRING(remarks,1,35) + ' '
+ SUBSTRING(remarks,36,81) + ' '
....

Makes no sense

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

X002548
Not Just a Number

15586 Posts

Posted - 2012-02-08 : 12:04:56

Are you trying to do a carriage return?

Won't the front end control wrap?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

Delinda
Constraint Violating Yak Guru

315 Posts

Posted - 2012-02-08 : 12:07:11

tq sir

Kristen
Test

22859 Posts

Posted - 2012-02-09 : 07:07:04

quote:
Originally posted by X002548

Are you trying to do a carriage return?

Won't the front end control wrap?

I don't think HTML will wrap - unless there are spaces or hyphens (or the hard-wired line break or some other tag). But I could be wrong ...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources