--
sql server 2000 用临时表
create table tb (id varchar ( 50 ), [ text ] varchar ( 12 ))
insert into tb values ( ' 1 ' , ' a ' )
insert into tb values ( ' 2 ' , ' a,b ' )
go
SELECT TOP 8000 id = IDENTITY ( int , 1 , 1 ) INTO tmp FROM syscolumns a, syscolumns b
select id , [ text ] = ' <u οnclick= '' http://www.baidu.com/s?wd=a '' > ' + [ text ] + ' </u> ' from
(
SELECT A.id, [ text ] = SUBSTRING (A. [ text ] , B.id, CHARINDEX ( ' , ' , A. [ text ] + ' , ' , B.id) - B.id)
FROM tb A, tmp B
WHERE SUBSTRING ( ' , ' + A. [ text ] , B.id, 1 ) = ' , '
) t
drop table tb , tmp
/**/ /*
id text
-------------------------------------------------- ---------------------------------------------------------
1 <u οnclick='http://www.baidu.com/s?wd=a'>a</u>
2 <u οnclick='http://www.baidu.com/s?wd=a'>a</u>
2 <u οnclick='http://www.baidu.com/s?wd=a'>b</u>
(所影响的行数为 3 行)
*/
create table tb (id varchar ( 50 ), [ text ] varchar ( 12 ))
insert into tb values ( ' 1 ' , ' a ' )
insert into tb values ( ' 2 ' , ' a,b ' )
go
SELECT TOP 8000 id = IDENTITY ( int , 1 , 1 ) INTO tmp FROM syscolumns a, syscolumns b
select id , [ text ] = ' <u οnclick= '' http://www.baidu.com/s?wd=a '' > ' + [ text ] + ' </u> ' from
(
SELECT A.id, [ text ] = SUBSTRING (A. [ text ] , B.id, CHARINDEX ( ' , ' , A. [ text ] + ' , ' , B.id) - B.id)
FROM tb A, tmp B
WHERE SUBSTRING ( ' , ' + A. [ text ] , B.id, 1 ) = ' , '
) t
drop table tb , tmp
/**/ /*
id text
-------------------------------------------------- ---------------------------------------------------------
1 <u οnclick='http://www.baidu.com/s?wd=a'>a</u>
2 <u οnclick='http://www.baidu.com/s?wd=a'>a</u>
2 <u οnclick='http://www.baidu.com/s?wd=a'>b</u>
(所影响的行数为 3 行)
*/
字符串连接问题 :
t
id text
1 a
2 a,b
由于某种原因只能在数据端加工,所以要得出这样的效果
text
<u οnclick='http://www.baidu.com/s?wd=a'>a </u>
<u οnclick='http://www.baidu.com/s?wd=a'>a </u>, <u οnclick='http://www.baidu.com/s?wd=a'>b </u>