利用SQLServer列拆分轻松实现字符串分割(sqlserver列拆分)
有时我们需要从字符串中提取多个片段,可以使用列拆分功能来轻松的实现字符串的分割,免去繁琐的`SUBSTRING`和`CHARINDEX`函数的组合。这篇文章将会一探究竟,展示如何利用SQLServer来实现字符串的分割功能。
#### 拆分字符串
这里使用示例表来模拟一个真实的应用场景,列 _Strs_ 中有一个字符串列表,每一行字符串记录了随机生成的4个单词,每个单词之间是由逗号分割的。
“`sql
CREATE TABLE #Strs
(
Str nvarchar(120)
)
```sqlINSERT INTO #Strs
VALUES ('ORANGE,DOG,MILK,MATCHA'), ('APPLE,CAT,SOY,BANANA'),
('BLACK,TIGER,TEA,STRAWBERRY')
此时,我们将会遇到的问题就是将以上的字符串取出每一个单词(不包括逗号),形成一张新的表,如下:
![image](https://static001.geekbang.org/resource/image/06/ableeditor/20200503180912_cqufkv.png)
涉及到这样的问题,我们经常会采用 `SUBSTRING` 和 `CHARINDEX` 等函数协同工作,例如下方代码:
“`sql
WITH SubStr AS
(
SELECT Str,
SUBSTRING(Str,1,CHARINDEX(‘,’,Str)-1)WORD1
FROM #Strs
)
SELECT Str,
WORD1,
SUBSTRING(Str, CHARINDEX(‘,’,Str)+1,
CHARINDEX(‘,’,SUBSTRING(Str,CHARINDEX(‘,’,Str)+1,LEN(Str))) -1)
AS WORD2
FROM SubStr
可以看出,使用最普通函数来处理字段分割问题的话,一定会面临到比较复杂的情况,加上循环嵌套等操作,代码犹如野兽一样复杂而又难以控制。
因此,可以利用SQLServer的列分割功能,将上述的复杂代码进行简化,例如如下的一小段代码:
```sqlSELECT Str,
WORD1, Value as WORD2
FROM #StrsCROSS APPLY
( SELECT Value
FROM dbo.splitStr(Str,',') WHERE NumericPosition = 2
) S
这里,我们在 `LINE 10` 使用了 _dbo.splitStr_ 函数,可以将一行字符串拆分成不同列,表示拆分结果。
此函数可以根据分隔符 `,` 将一行字符串拆分成不同量组,每个数据列后面加上一个虚拟偏移量,从 `1` 开始往上排列,在这里分别是 `WORD1` 和 `WORD2`。
最后,当拆分完成后,就可以得到目标分割表的结果
结论:使用列拆分功能可以有效轻松的实现字符串的分割,替代传统的 `SUBSTRING` 和`CHARINDEX` 等函数,减少复杂而又难于控制的循环嵌套操作,以更简便的方式实现字符串的拆分。
利用这一点,就可以轻松地处理一些常规的分割功能,例如将地址按省市区拆分,或将用户输入的组合参数的字符串进行拆分等……等各种场景下的应用体验提升。