SQLSERVER中WITH AS递归来实现ORACLE的INSTR函数以及固定分隔符字符串截取

分享个sqlserver的sql给大家，能用with as的递归实现oracle中的instr函数，并且能将分隔符的字符串解析，例如：

  字符串为：'O3O,30,4834,348934,AA,09WOFJOWE,FW'

  解析完成：

 

1）实现oracle的instr函数，sql如下：

WITH CTE1 AS (
SELECT '1' AS ID , 'O3O,30,4834,348934,AA,09WOFJOWE,FW' AS STR
),
CTE2 AS (
SELECT 1 AS LEVEL,ID,STR,CHARINDEX(',',STR,1) AS POSITION
FROM CTE1
UNION ALL 
SELECT LEVEL + 1 AS LEVEL,T.ID,T.STR,CHARINDEX(',',T.STR,POSITION + 1) AS POSITION
FROM CTE1 T JOIN CTE2 T1 ON T.ID = T1.ID
WHERE LEVEL < LEN(T.STR) - ISNULL(LEN(REPLACE(T.STR,',','')),0) 
)
SELECT * FROM CTE2 ;

 

    通过递归，第一次读取第一个逗号的位置，记为POSITION，然后递归，读取POSITION+1位置过后的第一个逗号的位置，递归次数由WHERE LEVEL < LEN(T.STR) - ISNULL(LEN(REPLACE(T.STR,',','')),0)去限制。

    取数据的时候，只要通过ID关联，取逗号出现第几（LEVEL）次出现的位置（POSITION）即可。

 

2）有了如上的instr实现，那么我们就可以将字符串截取出来，下面我们来看具体实现方法。

      首先，想法是这样的，既然有了POSITION，那么肯定是将第一个和第二个逗号的之间字符串截取，然后将第二个和第三个逗号之间字符串截取……第n-1个和第n个逗号之间的字符串截取。但是有个问题，'030'和'FW'，这两个字符串怎么办？其实很简单，给STR的第一个位置和最后一个位置都拼接上一个逗号即可。SQL如下：

WITH CTE1 AS (
SELECT '1' AS ID , 'O3O,30,4834,348934,AA,09WOFJOWE,FW' AS STR
),
CTE2 AS (
SELECT 1 AS LEVEL,ID,STR,',' + STR + ',' AS STR_NEW,CHARINDEX(',',',' + STR + ',',1) AS POSITION
FROM CTE1
UNION ALL 
SELECT LEVEL + 1 AS LEVEL,T.ID,T.STR,',' + T.STR + ',' AS STR_NEW,CHARINDEX(',',',' + T.STR + ',',POSITION + 1) AS POSITION
FROM CTE1 T JOIN CTE2 T1 ON T.ID = T1.ID
WHERE LEVEL < LEN(T.STR + ',') - ISNULL(LEN(REPLACE(T.STR + ',',',','')),0) + 1
)
SELECT * FROM CTE2 ;

 

 

    实现了如上的想法，我们只要用一个LEAD函数，将后面一个位置显示成新的一列，然后将字符串截取出来，SQL如下：

 

WITH CTE1 AS (
SELECT '1' AS ID , 'O3O,30,4834,348934,AA,09WOFJOWE,FW' AS STR
),
CTE2 AS (
SELECT 1 AS LEVEL,ID,STR,',' + STR + ',' AS STR_NEW,CHARINDEX(',',',' + STR + ',',1) AS POSITION
FROM CTE1
UNION ALL 
SELECT LEVEL + 1 AS LEVEL,T.ID,T.STR,',' + T.STR + ',' AS STR_NEW,CHARINDEX(',',',' + T.STR + ',',POSITION + 1) AS POSITION
FROM CTE1 T JOIN CTE2 T1 ON T.ID = T1.ID
WHERE LEVEL < LEN(T.STR + ',') - ISNULL(LEN(REPLACE(T.STR + ',',',','')),0) + 1
)
SELECT *,SUBSTRING(STR_NEW,POSITION + 1,LEAD(POSITION)OVER(ORDER BY POSITION) - (POSITION + 1)) AS JQ FROM CTE2 ;

 

 

   但是大家发现了，最后有一个空行，这个空的数据我们其实是不需要的，所以SQL修改为如下：

WITH CTE1 AS (
SELECT '1' AS ID , 'O3O,30,4834,348934,AA,09WOFJOWE,FW' AS STR
),
CTE2 AS (
SELECT 1 AS LEVEL,ID,STR,',' + STR + ',' AS STR_NEW,CHARINDEX(',',',' + STR + ',',1) AS POSITION
FROM CTE1
UNION ALL 
SELECT LEVEL + 1 AS LEVEL,T.ID,T.STR,',' + T.STR + ',' AS STR_NEW,CHARINDEX(',',',' + T.STR + ',',POSITION + 1) AS POSITION
FROM CTE1 T JOIN CTE2 T1 ON T.ID = T1.ID
WHERE LEVEL < LEN(T.STR + ',') - ISNULL(LEN(REPLACE(T.STR + ',',',','')),0) + 1
),
CTE3 AS (
SELECT LEVEL,ID,STR,STR_NEW,POSITION,LEAD(POSITION)OVER(ORDER BY POSITION) AS POSITION_NEXT
FROM CTE2
)
SELECT *,SUBSTRING(STR_NEW,POSITION + 1,POSITION_NEXT - (POSITION + 1)) AS JQ  FROM CTE3 
WHERE POSITION_NEXT IS NOT NULL ;

 

到此，我们所提的需求已经实现，大家可以自己测试一下吧，谢谢！

 注：此递归只适用于100个及其以下的分隔符的字符串进行分隔。