select substring(GoodsPlaceNo,1,2) as A,substring(GoodsPlaceNo,4,1) as B, substring(GoodsPlaceNo,8,1) as C,substring(GoodsPlaceNo,10,1) as D FROM TABLE主要就是substring函数。
--字符串分割 Declare @TUserID Table(UserID varchar(50)) Declare @chvTestString varchar(1024) Declare @i int,@i_t int Declare @chvString varchar(50) Set @chvString='' Set @chvTestString='1,23,456,6789,43,21' Set @i=1 Set @i_t=1 While @i<=len(@chvTestString) begin if SubString(@chvTestString,@i,1)=',' begin Set @chvString=SubString(@chvTestString,@i_t,@i-@i_t) Insert into @TUserID values(@chvString) Set @i_t=@i+1 end
Set @i=@i+1 end Insert into @TUserID values(SubString(@chvTestString,@i_t,@i-@i_t)) Select * from @TUserID
如果是选取,那么: (二楼的第二个长度不对) select substring(GoodsPlaceNo,1,2) as A,substring(GoodsPlaceNo,4,3) as B, substring(GoodsPlaceNo,8,1) as C,substring(GoodsPlaceNo,10,1) as D FROM TABLE如果是根据那个值得出新字段,那么:update yourTable set 字段a=substring(GoodsPlaceNo,1,2) ,字段b=substring(GoodsPlaceNo,4,3) , 字段c=substring(GoodsPlaceNo,8,1) ,字段d=substring(GoodsPlaceNo,10,1)
select substring(a,1,charindex('-',a)-1) as s1, substring(substring(a,charindex('-', a)+1, len(a)),1,charindex('-',substring(a,charindex('-', a)+1, len(a)))-1) as s2, substring(substring(a,charindex('-', a)+1,len(a)),len(a)+1-charindex(substring(substring(a,charindex('-', a)+1, len(a)),1,charindex('-',substring(a,charindex('-', a)+1, len(a)))-1),a),100) from #t
substring(GoodsPlaceNo,8,1) as C,substring(GoodsPlaceNo,10,1) as D FROM TABLE主要就是substring函数。
Declare @TUserID Table(UserID varchar(50))
Declare @chvTestString varchar(1024)
Declare @i int,@i_t int
Declare @chvString varchar(50)
Set @chvString=''
Set @chvTestString='1,23,456,6789,43,21'
Set @i=1
Set @i_t=1
While @i<=len(@chvTestString)
begin
if SubString(@chvTestString,@i,1)=','
begin
Set @chvString=SubString(@chvTestString,@i_t,@i-@i_t)
Insert into @TUserID values(@chvString)
Set @i_t=@i+1
end
Set @i=@i+1
end
Insert into @TUserID values(SubString(@chvTestString,@i_t,@i-@i_t))
Select * from @TUserID
http://blog.csdn.net/whbo/archive/2005/10/25/516318.aspx上面用","分开的串,--if SubString(@chvTestString,@i,1)=','
(二楼的第二个长度不对)
select substring(GoodsPlaceNo,1,2) as A,substring(GoodsPlaceNo,4,3) as B,
substring(GoodsPlaceNo,8,1) as C,substring(GoodsPlaceNo,10,1) as D FROM TABLE如果是根据那个值得出新字段,那么:update yourTable set 字段a=substring(GoodsPlaceNo,1,2) ,字段b=substring(GoodsPlaceNo,4,3) ,
字段c=substring(GoodsPlaceNo,8,1) ,字段d=substring(GoodsPlaceNo,10,1)
substring(substring(a,charindex('-', a)+1, len(a)),1,charindex('-',substring(a,charindex('-', a)+1, len(a)))-1) as s2,
substring(substring(a,charindex('-', a)+1,len(a)),len(a)+1-charindex(substring(substring(a,charindex('-', a)+1, len(a)),1,charindex('-',substring(a,charindex('-', a)+1, len(a)))-1),a),100)
from #t