SQL 语句 将表中用特殊字符分割的字段转换成多行数据
网上都有一些相关操作,但都比较复杂,写个比较简单的查询分割方式,
作为自己的备忘录,也供大家参阅
SELECT s.Item Engineer, d.BeginDate, d.EndDate FROM db_TestTemp AS d
CROSS APPLY [dbo].[StringSplit](Engineer, ',') AS s;
一、 创建字符串分割函数
CREATE FUNCTION [dbo].[StringSplit]
(
@ttstring NVARCHAR(MAX), --需要拆分的字符串
@separator CHAR --字符串中拆分符
)
RETURNS @temp TABLE
(
Item NVARCHAR(50)
)
AS
BEGIN
DECLARE @Item NVARCHAR(50)
DECLARE @CurrentIndex INT
DECLARE @NextIndex INT
DECLARE @Length INT --字符串的长度
SET @CurrentIndex=1
SET @Length=DATALENGTH(@ttstring)
IF @ttstring IS NOT NULL
BEGIN
WHILE @CurrentIndex < @Length
BEGIN
--CHARINDEX(子串,被搜索的字符串)
SET @NextIndex=CHARINDEX(@separator,@ttstring,@CurrentIndex)
IF @NextIndex=0
BEGIN
SET @NextIndex=@Length
END
SET @Item=SUB