;WITH TB(Col)AS( SELECT 'A1,B1,C1' UNION ALL SELECT 'A2,B2,C2' UNION ALL SELECT 'A3,B3,C3' ) SELECT LEFT(Col,2) ,SUBSTRING(Col,4,2) ,SUBSTRING(Col,7,2)--OR RIGHT(Col,2) FROM TB
Declare @str1 varchar(500),@str2 VARCHAR(500),@str3 VARCHAR(500) SET @str1='A1,B1,C1' SET @str2='A2,B2,C2' SET @str3='A3,B3,C3'DECLARE @Sql NVARCHAR(max) SET @Sql='WITH T1(str1,str2,str3) AS (SELECT '''+REPLACE(@str1+''' UNION ALL SELECT '''+@str2+''' UNION ALL SELECT '''+@str3,',',''',''')+''') SELECT * FROM T1' EXEC(@Sql) /* str1 str2 str3 A1 B1 C1 A2 B2 C2 A3 B3 C3 */
正则?下面是oracle中的写法,不知道这边是不是支持 SELECT regexp_substr(Col,'[^,]+',1,1) str1, regexp_substr(Col,'[^,]+',1,2) str2, regexp_substr(Col,'[^,]+',1,3) str3 FROM TB
; WITH T1 ( Col ) AS ( SELECT 'A1,B1,C1' UNION ALL SELECT 'A2,B2,C2' UNION ALL SELECT 'A3,B3,C3' ) SELECT str1 = LEFT(Col, CHARINDEX(',', Col) - 1) ,str2=SUBSTRING(Col,CHARINDEX(',', Col)+1,CHARINDEX(',', Col,CHARINDEX(',', Col)+1)-CHARINDEX(',', Col)-1) ,str3=RIGHT(Col,CHARINDEX(',',REVERSE(Col))-1) FROM T1 /* str1 str2 str3 A1 B1 C1 A2 B2 C2 A3 B3 C3 */
SELECT 'A1,B1,C1'
UNION ALL SELECT 'A2,B2,C2'
UNION ALL SELECT 'A3,B3,C3'
)
SELECT LEFT(Col,2)
,SUBSTRING(Col,4,2)
,SUBSTRING(Col,7,2)--OR RIGHT(Col,2)
FROM TB
SET @str1='A1,B1,C1'
SET @str2='A2,B2,C2'
SET @str3='A3,B3,C3'DECLARE @Sql NVARCHAR(max)
SET @Sql='WITH T1(str1,str2,str3) AS (SELECT '''+REPLACE(@str1+''' UNION ALL SELECT '''+@str2+''' UNION ALL SELECT '''+@str3,',',''',''')+''') SELECT * FROM T1'
EXEC(@Sql)
/*
str1 str2 str3
A1 B1 C1
A2 B2 C2
A3 B3 C3
*/
SELECT
regexp_substr(Col,'[^,]+',1,1) str1,
regexp_substr(Col,'[^,]+',1,2) str2,
regexp_substr(Col,'[^,]+',1,3) str3
FROM TB
WITH T1 ( Col )
AS ( SELECT 'A1,B1,C1'
UNION ALL
SELECT 'A2,B2,C2'
UNION ALL
SELECT 'A3,B3,C3'
)
SELECT str1 = LEFT(Col, CHARINDEX(',', Col) - 1)
,str2=SUBSTRING(Col,CHARINDEX(',', Col)+1,CHARINDEX(',', Col,CHARINDEX(',', Col)+1)-CHARINDEX(',', Col)-1)
,str3=RIGHT(Col,CHARINDEX(',',REVERSE(Col))-1)
FROM T1
/*
str1 str2 str3
A1 B1 C1
A2 B2 C2
A3 B3 C3
*/