把一个字段里的字符按特定的分隔符拆分成多个字段.
TABLE A
IDS
a,b,c,d,e,
请问如何把字段IDS里的字符按逗号拆分开放在TABLE B的五个字段里面?
ID_A ID_B ID_C ID_D ID_E
a b c d e
- CREATE TABLE TB (ID int, IDS nvarchar(100))
- INSERT INTO TB
- SELECT 1,'a,b,c,d,e'
- UNION ALL
- SELECT 2,'F,G,K,L,M,N'
- IF OBJECT_ID('DBO.SPLIT_STR') IS NOT NULL
- DROP FUNCTION DBO.SPLIT_STR
- GO
- CREATE FUNCTION DBO.SPLIT_STR(
- @S varchar(8000), --包含多个数据项的字符串
- @INDEX int, --要获取的数据项的位置
- @SPLIT varchar(10) --数据分隔符
- )
- RETURNS VARCHAR(100)
- AS
- BEGIN
- IF @S IS NULL RETURN(NULL)
- DECLARE @SPLITLEN int
- SELECT @SPLITLEN=LEN(@SPLIT+'A')-2
- WHILE @INDEX>1 AND CHARINDEX(@SPLIT,@S+@SPLIT)>0
- SELECT @INDEX=@INDEX-1,@S=STUFF(@S,1,CHARINDEX(@SPLIT,@S+@SPLIT)+@SPLITLEN,'')
- RETURN(ISNULL(LEFT(@S,CHARINDEX(@SPLIT,@S+@SPLIT)-1),''))
- END
- GO
- PRINT DBO.SPLIT_STR('AA|BB|CC',2,'|')
- --
- GO
- select ID, cast( DBO.SPLIT_STR(IDS,1,',') as nvarchar(2)) as a ,
- cast( DBO.SPLIT_STR(IDS,2,',') as nvarchar(2)) as b ,
- cast( DBO.SPLIT_STR(IDS,3,',') as nvarchar(2)) as c ,
- cast( DBO.SPLIT_STR(IDS,4,',') as nvarchar(2)) as d ,
- cast( DBO.SPLIT_STR(IDS,5,',') as nvarchar(2)) as e
- from
- TB
- /*
- ID a b c d e
- ----------- ---- ---- ---- ---- ----
- 1 a b c d e
- 2 F G K L M
- */