A
ASDFD,34.342
AED,453.2
SFSFSD,234,45
想得到的结果表
A B
ASDFD 34.342
AED 453.2
SFSFSD 234,45
sdfs,
sdfsf,
sdad,
使用XML方法:
Declare
@1
Table
(A
nvarchar
(
128
))
Insert Into @1
Select ' ASDFD,34.342 ' Union All
Select ' AED,453.2 ' Union All
Select ' SFSFSD,234,45 ' Union All
Select ' sdfs, ' Union All
Select ' sdfsf, ' Union All
Select ' sdad, '
; With T
As
(
Select A = Convert (Xml, ' <A> ' + Replace (A, ' , ' , ' , </A> <A> ' ) + ' </A> ' )
From @1
)
Select A = Convert ( nvarchar ( 128 ),A.query( ' data(/A[1]) ' )),
B = Convert ( nvarchar ( 128 ),A.query( ' /A[position()>1]/text() ' ))
From T
Insert Into @1
Select ' ASDFD,34.342 ' Union All
Select ' AED,453.2 ' Union All
Select ' SFSFSD,234,45 ' Union All
Select ' sdfs, ' Union All
Select ' sdfsf, ' Union All
Select ' sdad, '
; With T
As
(
Select A = Convert (Xml, ' <A> ' + Replace (A, ' , ' , ' , </A> <A> ' ) + ' </A> ' )
From @1
)
Select A = Convert ( nvarchar ( 128 ),A.query( ' data(/A[1]) ' )),
B = Convert ( nvarchar ( 128 ),A.query( ' /A[position()>1]/text() ' ))
From T