sqlserver拆分字串函式 及應用

Mr-Wanter發表於2016-05-09
SQLServer字串函式
USE [dataChange]
GO
/****** Object:  UserDefinedFunction [dbo].[f_splitSTR]    Script Date: 05/09/2016 15:04:21 <span style="color:#ff0000;">自己修改資料庫名，直接執行就可以了</span>******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[f_splitSTR](
	@s   varchar(8000),   --待分拆的字串
	@split varchar(10)     --資料分隔符
)RETURNS @re TABLE(
		col varchar(max))
AS
BEGIN
	DECLARE 
		@splitlen int

	-- 取分隔符的長度, 在分隔符後面加一個字元是為了避免分隔符以空格結束時, 取不到正確的長度
	SET @splitlen = LEN(@split + 'a') - 2
	-- 如果待分拆的字串中存在資料分隔符, 則迴圈取出每個資料項
	WHILE CHARINDEX(@split, @s)>0
	BEGIN
		-- 取第一個資料分隔符前的資料項
		INSERT @re VALUES(LEFT(@s, CHARINDEX(@split, @s) - 1))
		
		-- 將已經取出的第一個資料項和資料分隔符從待分拆的字串中去掉
		SET @s = STUFF(@s, 1, CHARINDEX(@split, @s) + @splitlen, '')
	END
	
	-- 儲存最後一個資料項(最後一個資料項後面沒有資料分隔符, 故在前面的迴圈中不會被處理)
	INSERT @re VALUES(@s)
	RETURN   
  end

應用sql：實現資料分割 如資料：

B41J-002/145%B41J-002/15%B41J-003/36%B41J-000/00%B43K-001/00%B41J-002/045%B41J-002/055%B41J-002/21%B43K-008/00%B81B-007/04%B43K-029/00%B43K-027/00%B43K-029/093  

取每一組%資料中的前一位組合起來用%分割 得到資料為

B%B%B%B%B%B%B%B%B%B%B%B%B

update [<span style="font-family: Arial, Helvetica, sans-serif;">dataChange</span><span style="font-family: Arial, Helvetica, sans-serif;">].[dbo].[Patent_Info]</span>
  set IPC_ALL1 = temp.IPC1
			from (
				 select PUB_NO,IPC1 = STUFF(
								(select '%'+ltrim(IPC1) 
								from 
									(select c.PUB_NO, SUBSTRING(COL,0,2) as IPC1
									from(
										Select B.*,A.IPC_ALL,A.PUB_NO FROM [<span style="font-family: Arial, Helvetica, sans-serif;">dataChange</span><span style="font-family: Arial, Helvetica, sans-serif;">].[dbo].[Patent_Info] A cross apply [</span><span style="font-family: Arial, Helvetica, sans-serif;">dataChange</span><span style="font-family: Arial, Helvetica, sans-serif;">].[dbo].f_splitSTR(IPC_ALL,'%')  B</span>
										)c 
								)tt
								where PUB_NO= t.PUB_NO for XML path('')
							),1,1,'')
					from 
					(select c.PUB_NO, SUBSTRING(COL,0,2) as IPC1
						from(
							Select B.*,A.IPC_ALL,A.PUB_NO FROM [<span style="font-family: Arial, Helvetica, sans-serif;">dataChange</span><span style="font-family: Arial, Helvetica, sans-serif;">].[dbo].[Patent_Info] A cross apply [</span><span style="font-family: Arial, Helvetica, sans-serif;">dataChange</span><span style="font-family: Arial, Helvetica, sans-serif;">].[dbo].f_splitSTR(IPC_ALL,'%')B)c</span><span style="font-family: Arial, Helvetica, sans-serif;">
</span>							)t							
					group by PUB_NO
			)temp
	where Patent_Info.PUB_NO = temp.PUB_NO


PUB_NO為資料表唯一欄位標識

相關文章