select left(D,charindex(',',D)) as column1, substring(D,charindex(',',D)+1,charindex(',',d,charindex(',',d)+1) - charindex(',',D)) as column2, right(d,len(d) - charindex(',',d,charindex(',',d,charindex(',',d)+1)+1)) as column3 from f
放入以下字段至查询分析器进行测试 use pubsdeclare @d varchar(20) set @D='10,200,300' select '第一段'=left(@d,2),'第二段'=substring(@d,4,3),'第3段'=right(@d,3) --结果--第一段 第二段 第3段 ---- ------ ------ 10 200 300(所影响的行数为 1 行)
修改一下:select left(D,charindex(',',D)-1) as column1, substring(D,charindex(',',D)+1,charindex(',',d,charindex(',',d)+1) - charindex(',',D)-1) as column2, right(d,len(d) - charindex(',',d,charindex(',',d)+1)) as column3 from f
测试环境: create table f ( d varchar(20) )insert into f select '10,200,300'--结果 -------------------------- column1 column2 column3 10 200 300 --------------------------
substring(D,charindex(',',D)+1,charindex(',',d,charindex(',',d)+1) - charindex(',',D)) as column2,
right(d,len(d) - charindex(',',d,charindex(',',d,charindex(',',d)+1)+1)) as column3
from f
use pubsdeclare @d varchar(20)
set @D='10,200,300'
select '第一段'=left(@d,2),'第二段'=substring(@d,4,3),'第3段'=right(@d,3)
--结果--第一段 第二段 第3段
---- ------ ------
10 200 300(所影响的行数为 1 行)
substring(D,charindex(',',D)+1,charindex(',',d,charindex(',',d)+1) - charindex(',',D)-1) as column2,
right(d,len(d) - charindex(',',d,charindex(',',d)+1)) as column3
from f
create table f
(
d varchar(20)
)insert into f
select '10,200,300'--结果
--------------------------
column1 column2 column3
10 200 300
--------------------------