我有这样一个表有一列:形式如下
col
[ad][ba][daf]
[erf][afda][dasfasdf]
[fas][fase][reb]
......
现在我想把这一列分成三列:
col1 col2 col3
[ad] [ba] [daf]
[erf] [afda] [dasfasdf]
......
请各位帮忙,急!!!
col
[ad][ba][daf]
[erf][afda][dasfasdf]
[fas][fase][reb]
......
现在我想把这一列分成三列:
col1 col2 col3
[ad] [ba] [daf]
[erf] [afda] [dasfasdf]
......
请各位帮忙,急!!!
解决方案 »
- 请问如何把Excel文件导入SqlServer2000中呢?
- 存储过程错误,对OUTPUT的应用,本人菜鸟求指导
- 如何根查询此列名属于哪几个表?
- 数据库问题
- 请看看这是什么错误造成的?!
- 急!!!如何分别比较两张表的行数!解决不出就要失业了,5555555555。。。。。。。。。。。
- 存储过程问题!
- 有没有什么SQL语句可以给已经存在的表添加一个列呢?
- I/O error (torn page) detected during read at offset 0x00000015304000 in file 'd:\mssql\data\*****_Data.MDF'
- 在SQL SERVER2000如何控制LOCAL PACKAGE的访问权限?
- 现统计每个人员的工资等级,请问SQL语句怎么写啊?谢谢
- 有没有简单的方法动态声明游标,就是声明和使用的游标名称每一次都不一样
insert into tb values('[ad][ba][daf]')
insert into tb values('[erf][afda][dasfasdf]')
insert into tb values('[fas][fase][reb]')
goselect
c1,
isnull(REPLACE(reverse(PARSENAME(c2,1)),';','.'),0) AS col2,
isnull(REPLACE(reverse(PARSENAME(c2,2)),';','.'),0) AS col3
from
(select
left(replace(col,'[',''),charindex(']',col)-2) as c1,
reverse(replace(replace(stuff(replace(col,'[',''),1,charindex(']',col),''),'.',';'),']','.')) as c2
from tb) a
drop table tb
insert into tb values('[ad][ba][daf]')
insert into tb values('[erf][afda][dasfasdf]')
insert into tb values('[fas][fase][reb]')
goselect
c1 col1,
isnull(REPLACE(reverse(PARSENAME(c2,1)),';','.'),0) AS col2,
isnull(REPLACE(reverse(PARSENAME(c2,2)),';','.'),0) AS col3
from
(
select
left(replace(col,'[',''),charindex(']',col)-2) as c1,
reverse(replace(replace(stuff(replace(col,'[',''),1,charindex(']',replace(col,'[','')),''),'.',';'),']','.')) as c2
from tb
) a
drop table tb/*
col1 col2 col3
----- ----- ----
ad ba daf
erf afda dasfasdf
fas fase reb(所影响的行数为 3 行)
*/
CREATE TABLE T
(
col varchar(8000)
)INSERT INTO T(col)
SELECT '[ad][ba][daf]' UNION ALL
SELECT '[erf][afda][dasfasdf]' UNION ALL
SELECT '[fas][fase][reb]'GOCREATE PROC F_CFCOLUMN
AS
BEGIN
DECLARE @i INT, @sql VARCHAR(8000)
SELECT @i = 1,@sql = ''WHILE EXISTS ( SELECT * FROM T WHERE col <>'')
BEGIN SELECT @sql = 'ALTER TABLE T ADD col'+CAST(@i AS VARCHAR)+' VARCHAR(30)'
EXEC(@sql)
SELECT @sql = 'UPDATE T SET col'+CAST(@i AS VARCHAR)+'=
SUBSTRING(col,1,CHARINDEX('']'',col)),
col=STUFF(col,1,CHARINDEX('']'',col),'''')',
@i = @i + 1
EXEC(@sql)
END
SELECT @sql = 'ALTER TABLE T DROP COLUMN col'
EXEC(@sql)
END
GO EXEC DBO.F_CFCOLUMN
SELECT * FROM T DROP PROC F_CFCOLUMN
DROP TABLE T col1 col2 col3
------------------------------ ------------------------------ ------------------------------
[ad] [ba] [daf]
[erf] [afda] [dasfasdf]
[fas] [fase] [reb](所影响的行数为 3 行)
insert @T select '[ad][ba][daf]'
insert @T select '[erf][afda][dasfasdf]'
insert @T select '[fas][fase][reb]'select
col1=left(col,charindex(']',col)),
col2=left(stuff(col,1,charindex(']',col),''),charindex(']',stuff(col,1,charindex(']',col),''))),
col3=right(col,charindex('[',reverse(col)))
from
@t/*[ad] [ba] [daf]
[erf] [afda] [dasfasdf]
[fas] [fase] [reb]
*/
(
@string VARCHAR(MAX),
@delimiter CHAR(1),
@rep int =1
)
RETURNS varchar(10)
BEGIN DECLARE @start INT, @end INT
SELECT @start = 1, @end = CHARINDEX(@delimiter, @string)
declare @ic int
set @ic = 1
WHILE @start < LEN(@string) + 1 BEGIN
if(@ic = @rep) return substring(@string, @start, @end-@start)
IF @end = 0
SET @end = LEN(@string) + 1 SET @start = @end + 1
SET @end = CHARINDEX(@delimiter, @string, @start)
set @ic = @ic + 1
END return null
END然后就可以放心大胆地用了, 别说三个了, 就是四个, 五个, 六个, 也不在话下.create table ta(col varchar(30))
insert into ta values('[ad][ba][daf]')
insert into ta values('[erf][afda][dasfasdf]')
insert into ta values('[fas][fase][reb]') select dbo.SplitString(col, ']', 1)+']' as col1,
dbo.SplitString(col, ']', 2)+']' as col2,
dbo.SplitString(col, ']', 3)+']' as col3
from tacol1 col2 col3
----------- ----------- -----------
[ad] [ba] [daf]
[erf] [afda] [dasfasdf]
[fas] [fase] [reb]