表A,内容如下:
RMANumber
--------------------------
QL_001,IEOOP,UG_Q001D,KDS99就是表在有RMANumber字段,RMANumber的值是QL_001,IEOOP,UG_Q001D,KDS99.我想让它以逗号为准把它分成几例. 结果如下:
例1 例2 例3 例4
---- ----- ----- -----
QL_001 IEOOP UG_Q001D KDS99
RMANumber
--------------------------
QL_001,IEOOP,UG_Q001D,KDS99就是表在有RMANumber字段,RMANumber的值是QL_001,IEOOP,UG_Q001D,KDS99.我想让它以逗号为准把它分成几例. 结果如下:
例1 例2 例3 例4
---- ----- ----- -----
QL_001 IEOOP UG_Q001D KDS99
另一种,就是你先添加4个空字段,写个SQL语句选第一个逗号前的给第一个空字段,第二
你看看SQL的字符操作函数就好解决了。
DECLARE @t TABLE(RMANumber varchar(100))
INSERT @t
SELECT RMANumber = 'QL_001,IEOOP,UG_Q001D,KDS99'-- 分拆处理
SELECT 例1, 例2,
例3 = LEFT(RMANumber, CHARINDEX(',', RMANumber+ ',') - 1),
例4 = STUFF(RMANumber, 1, CHARINDEX(',', RMANumber+ ','), '')
FROM(
SELECT 例1,
例2 = LEFT(RMANumber, CHARINDEX(',', RMANumber+ ',') - 1),
RMANumber = STUFF(RMANumber, 1, CHARINDEX(',', RMANumber+ ','), '')
FROM(
SELECT
例1 = LEFT(RMANumber, CHARINDEX(',', RMANumber+ ',') - 1),
RMANumber = STUFF(RMANumber, 1, CHARINDEX(',', RMANumber+ ','), '')
FROM @t
)A
)AA
QL_001 IEOOP UG_Q001D KDS99
Returns Varchar(400)
As
Begin
Declare @tmpStr varchar(400)
Declare @p int
Select @tmpStr=''
while @str<>''
Begin
select @p=charindex(',',@Str)
if @p>0
Begin
Select @tmpStr=@tmpStr+''''+ left(@Str,@p-1) +''','
Select @Str=right(@Str,len(@Str)-@P)
end
Else
Begin
Select @tmpStr=@tmpStr+''''+ @Str +''''
Select @Str=''
End end
select @tmpStr='Select ' +@tmpStr
Return @tmpStrend declare @strsplit varchar(500)
Select @strsplit=dbo.str_split('QL_001,IEOOP,UG_Q001D,KDS99')
exec (@strsplit)