全部
基础类
应用实例
新技术前沿

执行select显示某个字段的内容，怎样分行显示该内容？

liuzixi 2008-03-10 03:04:49

由于该字段是text类型，内容很长，结果在一行中看，很不方便

...全文

110 点赞收藏 5

写回复

5 条回复

切换为时间正序

当前发帖距今超过3年，不再开放新的回复

发表回复

leisure_cool 2008-03-11

学习

回复赞

qqhmitzk 2008-03-10

改下2楼的

if   exists   (select   1   from   sysobjects   where   name='tb'   and   type='u')     

  drop   table   tb   

go

  create   table   tb   (id1   int   , test varchar(8000))   

  insert   into   tb   select   1,'abcdefghijklmnopqrstuvwxyz'   

  union   select   2   ,'测试数据,测试数据测试数据测试数据'   

  union   select   3   ,'可将函数和变量声明为   table   类型。table   变量可用于函数、存储过程和批处理中。'   

go

if   exists   (select   1   from   sysobjects   where   name='getrowtext'   and   type='p')     

  drop   proc   getrowtext   

  go   

  create   proc   getrowtext   (@len   int)   as     

  begin   

  declare   @pos   int,   

  @i   int,   

  @times   int,   

  @id   int,   

  @text   varchar(255)   

  declare @tmptb   table(id1   int,   test   varchar(255))   

  declare   tb_cursor   cursor   for   select   id1,   test   from   tb   order   by   id1   

  open   tb_cursor   

  fetch   next   from   tb_cursor   into   @id,   @text   

  while   @@fetch_status   =   0   

  begin   

  set   @i=1   

  set   @pos=1   

  set   @times=len(@text)/@len+1   

  while   @i <=@times   

  begin   

  insert   into   @tmptb   select   @id,   substring(@text,@pos,@len)   

  set   @pos=@pos+@len   

  set   @i=@i+1   

  end   

  fetch   next   from   tb_cursor   into   @id,   @text   

  end   

  close   tb_cursor   

  deallocate   tb_cursor   

  select   *   from   @tmptb   

  end

最好用程序控制,SQL不是万能的

回复赞

qqhmitzk 2008-03-10

2楼用关键字做字段名,不妥把
能调试通过

回复赞

areswang 2008-03-10

--测试表
if exists (select 1 from sysobjects where name='tb' and type='u')
drop table tb
create table tb (id int ,text varchar(8000))
insert into tb select 1,'abcdefghijklmnopqrstuvwxyz'
union select 2 ,'测试数据,测试数据测试数据测试数据'
union select 3 ,'可将函数和变量声明为 table 类型。table 变量可用于函数、存储过程和批处理中。'

--创建存储过程
if exists (select 1 from sysobjects where name='getrowtext' and type='p')
drop proc getrowtext
go
create proc getrowtext (@len int) as
begin
declare @pos int,
@i int,
@times int,
@id int,
@text varchar(255)
declare @tmptb table(id int, text varchar(255))
declare tb_cursor cursor for select id, text from tb order by id
open tb_cursor
fetch next from tb_cursor into @id, @text
while @@fetch_status = 0
begin
set @i=1
set @pos=1
set @times=len(@text)/@len+1
while @i<=@times
begin
insert into @tmptb select @id, substring(@text,@pos,@len)
set @pos=@pos+@len
set @i=@i+1
end
fetch next from tb_cursor into @id, @text
end
close tb_cursor
deallocate tb_cursor
select * from @tmptb
end

--测试数据
exec getrowtext 10

回复赞