UPDATE TB SET COL1=ISNULL(COL1,''),COL2=ISNULL(COL2,''),COL3=ISNULL(COL3,0)... 按类型
DELETE TB WHERE COL IS NULL 删除? 更新? UPDATE TB SET COL=ISNULL(COL1,'')
-- ========================================= -- -----------t_mac 小编------------- ---希望有天成为大虾---- -- =========================================IF OBJECT_ID('tb') IS NOT NULL DROP TABLE tb GO CREATE TABLE tb(id INT IDENTITY,col VARCHAR(200)) go insert into tb SELECT '1,null,null,2,null,null,null,3,null' UNION ALL SELECT '2,null,3,null,null,null' go ----我想做的是将COL字段赋值 ~ NULL值更新为前一个值 --想变成 /* '1,1,1,2,2,2,2,3,3' '2,2,3,3,3,3' */ --我的想法是这样的 --首先拆分字段 SELECT TOP 1000 id = IDENTITY(int, 1, 1) INTO a FROM syscolumns a, syscolumns b SELECT A.id, SUBSTRING(A.col, B.id, CHARINDEX(',', A.col+ ',', B.id) - B.id) as value FROM tb A, a B WHERE SUBSTRING(',' + A.col, B.id, 1) = ',' order by A.id go--接着插入自增列 select idd = IDENTITY(int, 1, 1), A.id, SUBSTRING(A.col, B.id, CHARINDEX(',', A.col+ ',', B.id) - B.id) as value into ktv FROM tb A, a B WHERE SUBSTRING(',' + A.col, B.id, 1) = ',' order by A.id select * from ktv /* 1 1 1 2 1 null 3 1 null 4 1 2 5 1 null 6 1 null 7 1 null 8 1 3 9 1 null 10 2 2 11 2 null 12 2 3 13 2 null 14 2 null 15 2 null */ go /*update v set value=(select value from ktv where idd=v.idd-1 and ID=v.id) from ktv v where value= 'null' select * from ktv*/update v set value=(select top 1 value from ktv where ID=v.id and idd < v.idd and nullif(rtrim(ltrim(value)),'null') is not null order by idd desc) from ktv v where NULLIF(RTRIM(LTRIM(value)),'null') is null select * from ktv /*------------ 1 1 1 2 1 1 3 1 1 4 1 2 5 1 2 6 1 2 7 1 2 8 1 3 9 1 3 10 2 2 11 2 2 12 2 3 13 2 3 14 2 3 15 2 3 -------*/
set 字段=isnull(字段,' ')
按类型
删除?
更新?
UPDATE TB SET COL=ISNULL(COL1,'')
-- -----------t_mac 小编-------------
---希望有天成为大虾----
-- =========================================IF OBJECT_ID('tb') IS NOT NULL
DROP TABLE tb
GO
CREATE TABLE tb(id INT IDENTITY,col VARCHAR(200))
go
insert into tb
SELECT '1,null,null,2,null,null,null,3,null'
UNION ALL
SELECT '2,null,3,null,null,null'
go
----我想做的是将COL字段赋值 ~ NULL值更新为前一个值
--想变成
/*
'1,1,1,2,2,2,2,3,3'
'2,2,3,3,3,3'
*/
--我的想法是这样的
--首先拆分字段
SELECT TOP 1000 id = IDENTITY(int, 1, 1) INTO a FROM syscolumns a, syscolumns b
SELECT A.id, SUBSTRING(A.col, B.id, CHARINDEX(',', A.col+ ',', B.id) - B.id) as value
FROM tb A, a B
WHERE SUBSTRING(',' + A.col, B.id, 1) = ','
order by A.id
go--接着插入自增列
select idd = IDENTITY(int, 1, 1), A.id, SUBSTRING(A.col, B.id, CHARINDEX(',', A.col+ ',', B.id) - B.id) as value
into ktv
FROM tb A, a B
WHERE SUBSTRING(',' + A.col, B.id, 1) = ','
order by A.id
select * from ktv
/*
1 1 1
2 1 null
3 1 null
4 1 2
5 1 null
6 1 null
7 1 null
8 1 3
9 1 null
10 2 2
11 2 null
12 2 3
13 2 null
14 2 null
15 2 null
*/
go
/*update v
set value=(select value from ktv where idd=v.idd-1 and ID=v.id)
from ktv v
where value= 'null'
select * from ktv*/update v
set value=(select top 1 value from ktv
where ID=v.id and idd < v.idd
and nullif(rtrim(ltrim(value)),'null') is not null
order by idd desc)
from ktv v
where NULLIF(RTRIM(LTRIM(value)),'null') is null
select * from ktv
/*------------
1 1 1
2 1 1
3 1 1
4 1 2
5 1 2
6 1 2
7 1 2
8 1 3
9 1 3
10 2 2
11 2 2
12 2 3
13 2 3
14 2 3
15 2 3
-------*/