Split fields and merge fields are two problems that are common strings, which will be analyzed for example. Question: Spread a stroke into the original table Table1 (id int identity (1, 1), name varchar (100)) ID name ------------------- 1 'a, b, c'2' e, f '
I hope to get the result: ID name ------------ 1 'A'1' b'1 'c'2' e'2 'f'
First, first we use the most original approach to solve, that is, by loop by circulation. Create Table #a (ID Int Id Idness (1, 1), Name VARCHAR (100)) Insert #a (name) Select 'A, B, C'Union All Select' D, E, F'UNION All Select 'g, H'1. Using the Cursor Declare @ID INT, @ Name Varchar (100) Create Table #b (ID INT, Name Varchar (100)) Declare A_CURSOR CURSOR for Select ID, NameFrom #aorder by ID Open A_CURSOR FETCH NEXT from A_Cursor Into @ID, @name
While @@ fetch_status = 0begin While Charindex (',', @ name)> 0 Begin Insert #b (ID, Name) Select @ ID, Left (@ Name, Charindex (',', @ Name) -1) set @ Name = Right (@name, len (@Name) -charindex (',', @ name)) end insert #b (id, name) select @ id, @ name fetch next from a_cursor @ID, @NameEndClose a_cursordeallocate a_cursor
Select * from #bdrop Table # b2. Using Temporary Table instead of Cursor Declare @id Int, @ name varchar (100), @ count int int iDITY TABLE #TMP (no int tentity (1, 1), id int, name varchar (100) Create Table #b (ID INT, Name Varchar (100)) Insert #TMP (ID, Name) Select * from #aset @count = @@ RowCountWhile @count> 0begin select @ id = ID, @ name = name from # # TMP where no = @ Count While Charindex (',', @ name)> 0 Begin INSERT #B (ID, Name) Select @ ID, Left (@ Name, Charindex (', @ name) -1) set @ Name = Right (@name, len (@Name) -charindex (',', @ name)) end insert #b (id, name) select @ id, @ name set @ count = @ count-1ndselect * from #b ORDER BY ID, NAMEDROP TABLE # B, #TMP The above two methods have been used twice with cycling, clear and easy, but the code is long and slow.
Second, combine with the replace function and dynamic statement. - TEST: CREATE TABLE #A (ID Int Id Id Id IDITENTITY (1), Name Varchar (100)) Create Table #b (ID INT, Name Varchar (100)) Insert #a (name) Select 'A, B, C'union All Select 'D, E, F'UNION All Select' g, H'GodeClare @SQL VARCHAR (8000) Set @SQL = '' SELECT @SQL = @SQL 'INSERT INTO #B SELECT' CAST ( ID as varchar) ',' '' replace (name, ',', '' union all select ' Cast (ID as varchar) ', '') '' '' from # A - Print @SQLEXEC (@SQL) Gosent * from #bgodrop Table # a, # b
Analysis: This code is very concise, but use more complex dynamic statements and instead of the use of functions, and gives the program's interpretation of the program, and is limited by the length of the field skewers, once more than 8,000 characters can not meet the requirements .
Third, using the subString function calculated the up and down position of each segment, thereby achieving the purpose of the separation field. This method can handle the requirements of large data and the speed is faster, but this method is smart, it is difficult to associate. Create Table #a (ID Int IDENTITY (1, 1), Name Varchar (100))
INSERT #A (Name) Select 'A, B, C'union All Select' D, E, F'UNION All Select 'g, H'Select A.ID, Name = Substring (A.Name, B.ID, Charindex (',', a.name ',', b.id) -b.id) from #aa join (SELECT TOP 100 ID = A.ID B.ID 1 from (select id = 0 Union All Select 1 Union all select 3 union all select id = 4 union all select 5 union all select id = 6 union all select 7 union all select id = 8 Union All Select 9) A, (Select ID = 0 Union All Select) SELECT 10 Union All Select ID = 20 Union All Select 30 Union All Select ID = 40 Union All Select 50 Union All Select ID = 60 Union All SELECT 70 Union All Select ID = 80 Union All Select 90) B) B On Substring (' , ' a.Name, B.ID, 1) =', '
Drop Table #a integrates, the above three methods are compared to: the first kind of general, in line with the general people's writing ideas, but the efficiency is not high. The second method is short, and the ratio is also higher, but due to the length of the string, the versatility is not good, it is difficult to handle large quantities. The third method makes up for the shortcomings of the second method, but at the same time, the complexity of the program is added, and it is not easy to think about it.
Fourth, the split of NTEXT field
IF EXISTS (Select * from dbo.sysObjects where id = Object_id (n '[dbo]. [f_getstr]') and xtype in (n'fn ', n'if ", n')) DROP FUNCTION [DBO] [f_getstr] GO / * - Get the character string list Specify the location of the character can customize the separator of the string list If the range is exceeded, return to the empty string - Zou Jian 2005.04 (please keep this information) - * / / * - Call example - Test data declare @UserItem Table (useritemid int, catename varchar (50), otherdemo text) Insert @UserItem SELECT 1, 'Zhang San', '50 & Huangshi Xiaohe Town & 0762- 2262626 Union All SELECT 2, 'Li Si', '35 & Guangzhou & 020-2262626' Union All SELECT 3, 'Dr., '25 & Qingdao & 0456-2262626' Union All Select 4, 'Bachelor', '25 & Beijing & 010- 2262626 '- Demolition Select UserItemid, Catename, Age = DBO.F_GETSTR (OtherDemo, 1), Address = DBO.F_GETSTR (OtherDemo, 2), Contact Phone = DBO.f_getstr (OtherDemo, 3) from @UserItem / * - - Results UserItemid catename age address contact phone --------------------------- ---- --------- ------ 1 three 50 Huangshi City Xiaohe Town 0762-2262626 2 Li Si 35 Guangzhou 020-2262626 3 Dr. 25 Qingdao 0456-2262626 4 Bachelor 25 Beijing 010-2262626 (4 lines of the number of rows) - * / - * / CREATE FUNCTION F_GETSTR (@S ntext, - list of strings @ @ @ @S nText
