• 主页
• 基础类
• 应用实例
• 新技术前沿

# 求助！通过字符串中的分界符，把一列分为多列！

bqb 2007-12-17 03:21:30
``````
/*
sysbase 数据库

CNO                                    MESSAGE
-----------------------------------------------------
001                               2021 |AA |20070902|
002                               2022 |CC |20070905|
003                               2058 |BB |20070906|

CNO                    COLUMN1      COLUMN2      COLUMN3
-------------------------------------------------------------
001                     2021        AA        20070902
002                     2022        CC        20070905
003                     2058        BB        20070906
``````
...全文
382 点赞 收藏 41

41 条回复

shirley_yue 2008-01-16

shirley_yue 2008-01-16

wynlc 2008-01-16
--看一下，下边这个函数吧
CHARINDEX

CHARINDEX ( expression1 , expression2 [ , start_location ] )

expression1

expression2

start_location

yuelailiu 2008-01-16

``````
create table t(cno varchar(5),message varchar(50))
insert into t select '001','2021|AA|20070902|'
insert into t select '002','2022|CC|20070905|120|'
insert into t select '003','2058|BB|'

go
--分段截取函数
CREATE FUNCTION dbo.f_GetStr(
@s varchar(8000),      --包含多个数据项的字符串
@pos int,             --要获取的数据项的位置
@split varchar(10)     --数据分隔符
)RETURNS varchar(100)
AS
BEGIN
IF @s IS NULL RETURN(NULL)
DECLARE @splitlen int
SELECT @splitlen=LEN(@split+'a')-2
WHILE @pos>1 AND CHARINDEX(@split,@s+@split)>0
SELECT @pos=@pos-1,
@s=STUFF(@s,1,CHARINDEX(@split,@s+@split)+@splitlen,'')
RETURN(ISNULL(LEFT(@s,CHARINDEX(@split,@s+@split)-1),''))
END
GO

select
cno ,
dbo.f_GetStr (message,1,'|') as col1,
dbo.f_GetStr (message,2,'|') as col2,
dbo.f_GetStr (message,3,'|') as col3,
dbo.f_GetStr (message,4,'|') as col4
from
t

/*
001	2021	AA	20070902
002	2022	CC	20070905	120
003	2058	BB

*/``````

bqb 2008-01-16

bqb 2008-01-16

-狙击手- 2008-01-16

bqb 2008-01-16

bqb 2008-01-16

create Function StrSite(@Str varchar(2000) ,@Word varchar(20) ,@TimesNo int)
returns int
as
begin
declare @i int,@Times int
declare @WordLen int
set @Times=0
set @i=1
set @Wordlen=len(@Word)

while (@i< Len(@Str)+1 )
begin
if substring(@Str,@i,@Wordlen)=@word
set @times=@times+1
--print '第'+cast(@i as varchar(20)) +'是 : ' +cast(@Times as varchar(20))

if @times=@TimesNo
break
set @i=@i+1
end
return (@i)
end

dawugui 2008-01-15
--分解字符串的函数.

``````/*

*/

create function dbo.fn_split
(
@inputstr varchar(8000),
@seprator varchar(10)
)
returns @temp table (a varchar(200))
as

begin
declare @i int

set @inputstr = rtrim(ltrim(@inputstr))
set @i = charindex(@seprator, @inputstr)

while @i >= 1
begin
insert @temp values(left(@inputstr, @i - 1))

set @inputstr = substring(@inputstr, @i + 1, len(@inputstr) - @i)
set @i = charindex(@seprator, @inputstr)
end

if @inputstr <> '\'
insert @temp values(@inputstr)

return
end
go

--调用

declare @s varchar(1000)

set @s='1,2,3,4,5,6,7,8,55'

select * from dbo.fn_split(@s,',')

drop function dbo.fn_split ``````

dawugui 2008-01-15

bqb 2008-01-15

set 也不能用
declare @a table（id int ）定义表变量 也不能用

chuifengde 2008-01-08
``````create table tk(cno varchar(5),message varchar(50))
insert into tk select '001','2021|AA|20070902|'
insert into tk select '002','2022|CC|20070905|120|'
insert into tk select '003','2058|BB|'

go
create proc GetCol
as
set nocount on
declare @max int,@i int
declare @sql varchar(8000)

set @i=1
select @max=max(len(message)-len(replace(message,'|',''))) from tk

select cno,message+replicate('|',@max-len(message)+len(replace(message,'|',''))) message into tkx from tk

while @i<=@max
begin
select @sql=isnull(@sql+',','')+'col'+ltrim(@i)+' varchar(50)',@i=@i+1
end
set @sql='create table tkxx(cno varchar(5),'+@sql+')'
exec(@sql)

set @i=1
insert tkxx(cno) select cno from tkx

while @i<=@max
begin
set @sql='
declare @a table(cno varchar(5),head varchar(50));
insert @a select cno,left(message,charindex(''|'',message)-1) from tkx;
update tkx set message=stuff(message,1,charindex(''|'',message),'''');
update tkxx set col'+ltrim(@i)+'=head from @a a where tkxx.cno=a.cno;'
exec(@sql)
set @i=@i+1
end
select * from tkxx
drop table tkx,tkxx

go
exec getCol
--result
/*
cno   col1                           col2                           col3                           col4
----- ------------------------------ ------------------------------ ------------------------------ ------------------------------
001   2021                           AA                             20070902
002   2022                           CC                             20070905                       120
003   2058                           BB
*/``````

bqb 2008-01-08

pt1314917

pt1314917 2008-01-03
``````

create table tk(cno varchar(5),message varchar(50))
insert into tk select '001','2021|AA|20070902|'
insert into tk select '002','2022|CC|20070905|120|'
insert into tk select '003','2058|BB|'

--创建函数：

create function sssss(@cno varchar(10))
returns varchar(2000)
as
begin
declare @message varchar(2000)
declare @i int,@num int,@c int
set @i=1
select @message=message from tk where cno=@cno
select @num=max(len(replace(message,' ',''))-len(replace(replace(message,' ',''),'|',''))) from tk
set @c=len(replace(@message,' ',''))-len(replace(replace(@message,' ',''),'|',''))
set @message=@message+replicate(' |',@num-@c)
while(charindex('|',@message)>0)
begin
set @message=stuff(@message,charindex('|',@message),1,''' columns'+cast(@i as varchar)+',''')
set @i=@i+1
end
set @message=left(@message,len(@message)-2)
return ''''+@cno+''' cno,'''+@message
end
go

--创建存储过程
create proc wsp
as
declare @sql varchar(8000)
select @sql=isnull(@sql+' union select ','select ')+dbo.sssss(cno) from tk
exec(@sql)

--执行存储过程
exec wsp

``````

bqb 2008-01-03

bqb 2008-01-03

dawugui

dawugui

SQL code--SQL SERVER的写法如下:
``````create table tb(CNO varchar(10), MESSAGE varchar(30))
insert into tb values('001', '2021|AA|20070902|')
insert into tb values('002', '2022|CC|20070905|')
insert into tb values('003', '2058|BB|20070906|')
go

select cno ,
PARSENAME(replace(left(MESSAGE,len(message)-1),'|','.'),3) column1,
PARSENAME(replace(left(MESSAGE,len(message)-1),'|','.'),2) column2,
PARSENAME(replace(left(MESSAGE,len(message)-1),'|','.'),1) column3
from tb

drop table tb

/*
cno        column1                                                                                                                          column2                                                                                                                          column3
---------- -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------
001        2021                                                                                                                             AA                                                                                                                               20070902
002        2022                                                                                                                             CC                                                                                                                               20070905
003        2058                                                                                                                             BB                                                                                                                               20070906

(3 行受影响)
*/

``````

qiule 2008-01-03

bqb 2008-01-03
SQL 就是人多，高手也多！

MS-SQL Server

3.2w+

MS-SQL Server相关内容讨论专区