案例:
表:
Create table dbo.subscribe
(
ID int identity(1,1) not null primary key,
emailTo varchar(500)
)
数据:
INSERT INTO dbo.subscribe VALUES('lei@live.com;min@live.com')
INSERT INTO dbo.subscribe VALUES('tom@live.com')
INSERT INTO dbo.subscribe VALUES('lei.min@live.com;tom@newegg.net;kim@newegg.com')
现在subscribe中有三行数据,
ID emailTo
1 lei@live.com;min@live.com
2 tom@live.com
3 lei.min@live.com;tom@newegg.net;kim@newegg.com
需求:
现在需要按";"拆分字段,然后提供为下面的形式
ID emailTo
1 lei@live.com
1 min@live.com
2 tom@live.com
3 lei.min@live.com
3 tom@newegg.net
3 kim@newegg.com
解决方案:
--1.这个输入一个需要拆分的字符串,返回拆分好并装进表中
CREATE FUNCTION dbo.fn_getString(@ID INT,@Email VARCHAR(200))
RETURNS @return_variable TABLE(ID INT, Email VARCHAR(200) NOT NULL)
AS
BEGIN
WHILE PATINDEX('%;%',@Email)<>0
BEGIN
INSERT INTO @return_variable(ID,Email)
SELECT @ID,STUFF(@Email,PATINDEX('%;%',@Email),(LEN(@Email)-PATINDEX('%;%',@Email))+1,'')
SET @Email=STUFF(@Email,1,PATINDEX('%;%',@Email),'')
END
INSERT INTO @return_variable(ID,Email)
SELECT @ID,@Email
RETURN
END
GO
--2-5一次执行
--2.定义存储拆分后的数据的临时表
DECLARE @t TABLE
(ID INT,EmailAddress NVARCHAR(200) NOT NULL)
DECLARE @i INT--递增的ID
DECLARE @j INT--总数据行
SELECT @i=1
,@j=ISNULL(MAX(id),0)
FROM subscribe
WHILE @i<=@j
BEGIN
DECLARE @emailaddress NVARCHAR(200)--拆分前某ID 的emailaddress
DECLARE @ID INT--某ID
IF EXISTS(SELECT TOP 1 1 FROM subscribe WHERE ID=@i)--过滤掉中间没有该ID 的数据行
BEGIN
--3.读取某ID的emailAddress
SELECT @emailaddress=EmailTo,@ID=ID
FROM subscribe
WHERE ID=@i
--4.将拆分后的emailAddress插入临时表
INSERT @t(ID,EmailAddress)
SELECT ID,Email
FROM dbo.fn_getString(@ID,@emailaddress)
SET @i=@i+1
END
ELSE
SET @i=@i+1
END
--5.察看结果
SELECT *
FROM @t
GO
希望对有类似需求的朋友有所帮助!