SQL Server split string function and its application

Use [datachange]go/****** Object:  userdefinedfunction [dbo].[ F_SPLITSTR]    Script date:05/09/2016 15:04:21 <span style= "color: #ff0000;" > Modify the database name yourself, and execute it directly </span>******/set ansi_nulls ongoset quoted_identifier ongoalter FUNCTION [dbo]. [F_splitstr] (@s   varchar (8000),   --string to be split @split varchar ()-     -data delimiter) RETURNS @re TABLE (col varchar (max)) Asbegindeclare @splitlen int--The length of the delimiter, adding a character after the delimiter to prevent the delimiter from ending with a space, not taking the correct length set @splitlen = LEN (@split + ' a ')-2--if the word to be split Data delimiter is present in the string, the CHARINDEX (@split, @s) >0begin--takes the data item before the first data delimiter insert @re VALUES (left (@s, CHARINDEX (@ Split, @s)--Remove the first data item and data delimiter that has been removed from the string to be split set @s = STUFF (@s, 1, CHARINDEX (@split, @s) + @splitlen, ") end--Save the last data Item (no data delimiter after the last data item, so it will not be processed in the previous loop) INSERT @re VALUES (@s) RETURN     End

Apply SQL: Implement data segmentation such as data:

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

Take the previous digit in each set of% data together to get the data in% split

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.  Ipc1from (Select pub_no,ipc1 = STUFF ((select '% ' +ltrim (IPC1) from (select C.pub_no, SUBSTRING (col,0,2) as Ipc1from (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) ttwhere pub_no= t.pub_no for XML Path (")), ("), ") from (select C.pub_no , SUBSTRING (col,0,2) as Ipc1from (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>) Tgroup by pub_no) tempwhere Patent_info.pub_no = temp. Pub_no

Pub_no a unique field identifier for a data table

SQL Server split string function and its application