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/1787138
MS SQL Splits commas and retrieves fields