MS SQL Splits commas and retrieves fields

create table curtest  (Djbh varchar)  primary key,sales varchar (20)) goinsert curtestselect  ' A1 ', ' 01,02,03 '  union all            --CREATE TABLE and insert record select  ' A2 ', ' 01,02 '   union allselect  ' A3 ', ' 02,03 '   union allselect  ' A4 ', ' 02,03,04 '   godeclare cur_test cursor for  select * from curtest    --declaration cursor declare  @djbh  varchar (20)                              --defining variable Document number declare  @sales  varchar (20)                              --define Variable sales declare  @star  int, @len  int, @dhwz  int                --  define variable start, length, comma position open  cur_test                                          --Open the cursor fetch next from cur_test into  @djbh, @sales             --get the first record while (@ @FETCH_STATUS =0)                      The              --confirms the execution result based on the return status. 0 stands for Success beginset  @star =1                                              --to start position assignment 1set  @dhwz =charindex (', ', @sales, @star)                  --gets the first comma position set  @len =len (@sales)                                  --Gets the field length while (@star <[ Email protected])                                       --loop condition, start position is less than field length begin                                              select @ Djbh,substring (@sales, @star, @[email protected])         --get comma before character  set @[email protected]+1                                           --Each loop, place the last comma position +1 as the next starting position set  @dhwz =charindex (', ', @sales, @star)                     --with the last comma position +1 as the starting position, Retrieves the next comma position if (@dhwz =0)                                                   --, when a comma is not retrieved (that is, the return value is 0, and has reached the last comma) beginselect  @djbh, SUBSTRING (@sales, @star, (@[email protected]) +1)       --the last comma after character, above once the comma position +1 starts, the length is the total field length minus start position +1break                                                       --jump out of the loop     end    endfetch  next from cur_test into  @djbh, @sales                --reads the next record and restarts the loop (splitting and retrieving comma-delimited characters) endclose cur_test

650) this.width=650; "src=" Http://s1.51cto.com/wyfs02/M02/82/7D/wKioL1dW4OOBm4BWAAAGSgU3rXE770.png "title=" QQ picture 20160607223721.png "alt=" Wkiol1dw4oobm4bwaaagsgu3rxe770.png "/>

650) this.width=650; "src=" Http://s2.51cto.com/wyfs02/M02/82/7D/wKioL1dW4KaRIKwIAAAVnxUhyas782.png "style=" float: none; "title=" Qq20160607225147.png "alt=" Wkiol1dw4karikwiaaavnxuhyas782.png "/>

This article is from the "Marsng" blog, make sure to keep this source http://marsng.blog.51cto.com/2693748/1787136

MS SQL Splits commas and retrieves fields