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个及其以下的分隔符的字符串进行分隔。