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

执行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
回复
dawugui 2008-03-10
这不是SQL的功能吧?

自己在前台通过软件来做.
回复
相关推荐
发帖
MS-SQL Server
创建于2007-09-28

3.3w+

社区成员

MS-SQL Server相关内容讨论专区
申请成为版主
帖子事件
创建了帖子
2008-03-10 03:04
社区公告
暂无公告