SQL数据转换技巧-优快云博客

http://topic.youkuaiyun.com/u/20090918/23/4df5e104-f956-4e5c-8811-3317646a0878.html?11129

declare @a table(thedate datetime ,val text)
insert into @a
select '2009-09-07', '星期一 08:00 起床刷牙洗脸 9:15 开始上班 12:00 午休 2:5 下午上班' union all
select '2009-09-08', '星期二 07:50 起床刷牙洗脸 9:15 开始上班 12:00 午休 2:5 下午上班'

declare @b table(thedate datetime,col varchar(100))

我要把表@a中的数据转换成下面的格式保存到@b表中，因为@a表中的val字段里时间的格式不一至，所以比较麻烦，等待高手。。。分数不够可以再加
thedate col
2009-09-07 00:00:00.000 星期一
2009-09-07 08:00:00.000 起床刷牙洗脸
2009-09-07 09:15:00.000 开始上班
2009-09-07 12:00:00.000 午休
2009-09-07 02:05:00.000 下午上班
2009-09-08 00:00:00.000 星期二
2009-09-08 07:50:00.000 起床刷牙洗脸
2009-09-08 09:15:00.000 开始上班
2009-09-08 12:00:00.000 午休
2009-09-08 02:05:00.000 下午上班

------------------------------------------

declare @a table(thedate datetime,val text)
insert into @a
select '2009-09-08', '星期一 08:00 起床刷牙洗脸 9:15 开始上班 12:00 午休 2:5 下午上班' union all
select '2009-09-10', '星期二 07:50 起床刷牙洗脸 9:15 开始上班 12:00 午休 2:5 下午上班'

declare @b table(thedate datetime
,col varchar(100))

DECLARE @a1 DATETIME,@b1 VARCHAR(800)
DECLARE cur CURSOR FOR SELECT * FROM @a
OPEN cur
FETCH NEXT FROM cur INTO @a1,@b1
WHILE @@FETCH_STATUS=0
BEGIN
SET @b1=REPLACE(@b1,' ',',')+','
WHILE LEN(@b1)>0
BEGIN
IF ISDATE(LEFT(@b1,CHARINDEX(',',@b1)-1))=1
BEGIN
SET @a1=@a1+CAST(LEFT(@b1,CHARINDEX(',',@b1)-1) AS DATETIME)
SET @b1=STUFF(@b1,1,CHARINDEX(',',@b1),'')
END

INSERT @b SELECT @a1,LEFT(@b1,CHARINDEX(',',@b1)-1)
SET @a1=CONVERT(VARCHAR,@a1,23)

SET @b1=STUFF(@b1,1,CHARINDEX(',',@b1),'')
END
FETCH NEXT FROM cur INTO @a1,@b1
END
CLOSE cur
DEALLOCATE cur

SELECT * FROM @b
/*
thedate col
----------------------- ------------------------
2009-09-07 00:00:00.000 星期一
2009-09-07 08:00:00.000 起床刷牙洗脸
2009-09-07 09:15:00.000 开始上班
2009-09-07 12:00:00.000 午休
2009-09-07 02:05:00.000 下午上班
2009-09-08 00:00:00.000 星期二
2009-09-08 07:50:00.000 起床刷牙洗脸
2009-09-08 09:15:00.000 开始上班
2009-09-08 12:00:00.000 午休
2009-09-08 02:05:00.000 下午上班

(10 行受影响)
*/

--高级需求:

declare @a table(thedate datetime,val text)
insert into @a
select '2009-09-08', '星期一 08:00 起床刷牙洗脸 9:15 开始上班12:00午休2:5下午上班' union all
select '2009-09-10', '星期二07:50起床刷牙洗脸9:15开始上班12:00午休2:5下午上班' union all
select '2009-09-11', '07:50起床刷牙洗脸9:15开始上班12:00午休2:5下午上班' union all
select '2009-09-12', '07:50起床刷牙洗脸9:15开始上班12:00午休2:5下午上班15:00'

declare @b table(thedate datetime ,col varchar(20))
SET NOCOUNT ON
DECLARE @a1 DATETIME,@b1 VARCHAR(800)
DECLARE cur CURSOR FOR SELECT * FROM @a
OPEN cur
FETCH NEXT FROM cur INTO @a1,@b1
WHILE @@FETCH_STATUS=0
BEGIN
WHILE LEN(@b1)>0
BEGIN
SET @b1=REPLACE(@b1,' ','') --把所有空格通通去掉
IF PATINDEX('%[0-9:]',@b1)>0 --如果末位为时间,加个字符
SET @b1=@b1+'<未安排>'
BEGIN
IF ISDATE(LEFT(@b1,PATINDEX('%[^0-9:]%',@b1)-1))=1 --如果存在时间格式
BEGIN --时间加到日期进行计算
SET @a1=@a1+CAST(LEFT(@b1,PATINDEX('%[^0-9:]%',@b1)-1) AS DATETIME)
SET @b1=STUFF(@b1,1,PATINDEX('%[^0-9:]%',@b1)-1,'')
END
SET @b1=@b1+'0'
INSERT @b SELECT @a1,LEFT(@b1,PATINDEX('%[0-9]%',@b1)-1) --插入结果
SET @b1=LEFT(@b1,LEN(@b1)-1)
SET @a1=CONVERT(VARCHAR,@a1,23) --初始化日期
SET @b1=STUFF(@b1,1,PATINDEX('%[0-9]%',@b1)-1,'')
IF @b1='<未安排>'
SET @b1=''
END
END
FETCH NEXT FROM cur INTO @a1,@b1
END
CLOSE cur
DEALLOCATE cur
SET NOCOUNT OFF

SELECT * FROM @b
/*
thedate col
----------------------- --------------------
2009-09-08 00:00:00.000 星期一
2009-09-08 08:00:00.000 起床刷牙洗脸
2009-09-08 09:15:00.000 开始上班
2009-09-08 12:00:00.000 午休
2009-09-08 02:05:00.000 下午上班
2009-09-10 00:00:00.000 星期二
2009-09-10 07:50:00.000 起床刷牙洗脸
2009-09-10 09:15:00.000 开始上班
2009-09-10 12:00:00.000 午休
2009-09-10 02:05:00.000 下午上班
2009-09-11 07:50:00.000 起床刷牙洗脸
2009-09-11 09:15:00.000 开始上班
2009-09-11 12:00:00.000 午休
2009-09-11 02:05:00.000 下午上班
2009-09-12 07:50:00.000 起床刷牙洗脸
2009-09-12 09:15:00.000 开始上班
2009-09-12 12:00:00.000 午休
2009-09-12 02:05:00.000 下午上班
2009-09-12 15:00:00.000 <未安排>

(19 行受影响)
*/