假设您使用的是 SQL Server,您可以结合使用 SUBSTRING ( http://msdn.microsoft.com/en-us/library/ms187748.aspx ) 和 CHARINDEX( http://msdn.microsoft.com/en-us/library/ms186323.aspx ) 来实现此目的:
SELECT
CHARINDEX('/',MyColumn) AS P1,
CHARINDEX('/',MyColumn,CHARINDEX('/',MyColumn)+1) AS P2,
CHARINDEX('/',MyColumn,CHARINDEX('/',MyColumn,CHARINDEX('/',MyColumn)+1)+1) AS P3
FROM MyTable;
这将为您提供三个 /
字符的位置。要拆分字段,请使用:
SELECT
SUBSTRING(transactionId,1,P1-1) AS A,
SUBSTRING(transactionId,P1+1,P2-P1-1) AS B,
SUBSTRING(transactionId,P2+1,P3-P2-1) AS C,
SUBSTRING(transactionId,P3+1,LEN(transactionId)) AS D
FROM(
SELECT
CHARINDEX('/',transactionId) AS P1,
CHARINDEX('/',transactionId,CHARINDEX('/',transactionId)+1) AS P2,
CHARINDEX('/',transactionId,CHARINDEX('/',transactionId,CHARINDEX('/',transactionId)+1)+1) AS P3,
transactionId
FROM dbo.MyTable
)X;
最后,如果您使用的是 2005 或更高版本,则可以使用 UNPIVOT (http://msdn.microsoft.com/en-us/library/ms177410(v=sql.105).aspx) 将值分隔成多行:
SELECT U.*
FROM (
SELECT
SUBSTRING(transactionId,1,P1-1) AS A,
SUBSTRING(transactionId,P1+1,P2-P1-1) AS B,
SUBSTRING(transactionId,P2+1,P3-P2-1) AS C,
SUBSTRING(transactionId,P3+1,LEN(transactionId)) AS D
FROM(
SELECT
CHARINDEX('/',transactionId) AS P1,
CHARINDEX('/',transactionId,CHARINDEX('/',transactionId)+1) AS P2,
CHARINDEX('/',transactionId,CHARINDEX('/',transactionId,CHARINDEX('/',transactionId)+1)+1) AS P3,
transactionId
FROM dbo.MyTable
)X
)Y
UNPIVOT (Data for ColumnName IN(A,B,C,D))U ;
这是一个SQL Fiddle
现在,性能完全是另一个话题。 SQL Server 天生不擅长字符串操作。您可以使用 CLR 显着加快速度字符串拆分函数。
我是一名优秀的程序员,十分优秀!