SQL loop table data, split Function

There are two small knowledge points. To make it easier for you to read the information, you also need to develop a habit. A blog writing habit. I read it last time.Article. It is about the benefits of blog writing.

I have read it, and I have some feelings. I also know that I may not be able to stick to it. But I want to try it. Try to develop such a habit.

1. Data in the SQL loop table

 Select   *   Into # T1 From  Userinfo  Declare   @ ID   Varchar ( 2000 );  While ( Exists ( Select Projectid From  # T1 ))  Begin       Select   @ ID  = Userid From  # T1  Delete # T1 Where Projectid = @ ID  End  Drop   Table # T1

2 SQL sharding Functions

 Use   [  Xsman_db  ]  Go  /*  * *** Object: userdefinedfunction [DBO]. [func_splittotable] script Date: 12/03/2012 23:25:37 *****  */  Set Ansi_nulls On Go  Set Quoted_identifier On  Go  --  ========================================================== =====  --  Author: cxy  --  Create Date: 2010-10-28  --  Description: splits the string according to the delimiter and returns the table  --  ========================================================== =====  Create  Function   [  DBO  ] . [  Func_splittotable  ]  (  @ Splitstring   Nvarchar ( Max  ),  @ Separator   Nvarchar ( 10 ) = '   '  )  Returns   @ Splitstringstable   Table  (  [  ID  ]   Int   Identity ( 1 , 1  ),  [ Value  ]   Nvarchar ( Max  ))  As    Begin     Declare   @ Currentindex   Int  ;  Declare   @ Nextindex   Int  ;  Declare   @ Returntext  Nvarchar ( Max  );  Select   @ Currentindex  =  1  ;  While ( @ Currentindex  <=  Len ( @ Splitstring  ))  Begin      Select  @ Nextindex  =  Charindex ( @ Separator , @ Splitstring , @ Currentindex  );  If ( @ Nextindex  =  0   Or   @ Nextindex   Is   Null  ) Select   @ Nextindex  =  Len ( @ Splitstring ) +  1  ;  Select   @ Returntext  =  Substring ( @ Splitstring , @ Currentindex , @ Nextindex -  @ Currentindex  );  Insert   Into   @ Splitstringstable ( [  Value  ] ) Values ( @ Returntext  );  Select   @ Currentindex  =  @ Nextindex +  1  ;  End     Return    End  --  Execute function 1  Select   *   From DBO. func_splittotable ( '  1, 2  ' , '  ,  ' )  --  Execute function 2  Select   *   From DBO. func_splittotable ( '  1-2-3  ' , '  -  '  )  --  Execute function 3  Select   *  From DBO. func_splittotable ( '  1 2 3  ' , '   ' )

 

Execution result:

 

I hope I can stick to it. Effort!