Delimiter string table Value Function

In practice, we usually have such a business scenario. We need to split a large string with a specific string to generate a table. The method below solves this problem very well.

1 If exists (select 1 from sysobjects where id = object_id ('fn _ Split ') and xtype = 'fn') 2 begin 3 drop function DBO. fn_split 4 end 5 go 6 -- function: Split string table value function 7 -- parameter number: @ string split string 8 -- @ delimiter separator 9 -- return value: table 10 -- created by: mao000011 -- created at: 2014-09-2412 create function [DBO]. [fn_split] (13 @ string nvarchar (max), 14 @ delimiter nvarchar (10) 15) 16 returns @ valuetable table ([value] nvarchar (max), [ID] INT) 17 begin 18 declare @ nextstring nvarchar (max), 19 @ POS int, 20 @ nextpos int, 21 @ commacheck nvarchar (1 ), 22 @ ID int23 24 set @ ID = 1 25 26 set @ nextstring = ''27 Set @ commacheck = right (@ string, 1) 28 29 set @ string = @ string + @ delimiter 30 31 set @ Pos = charindex (@ delimiter, @ string) 32 set @ nextpos = 1 33 34 while (@ POS <> 0) 35 begin 36 set @ nextstring = substring (@ string, 1, @ pos-1) 37 38 insert into @ valuetable ([value], [ID]) values (@ nextstring, @ ID) 39 40 set @ string = substring (@ string, @ POS + 1, len (@ string) 41 42 set @ nextpos = @ POS 43 set @ Pos = charindex (@ delimiter, @ string) 44 45 set @ ID = @ ID + 146 end 47 48 return 49 end 50 go

View code

 

Delimiter string table Value Function