--
Ms SQL server2k string splitting and merge processing code
-- Code for string splitting
Create Function f_splitstr (
@ S varchar (8000), -- string to be split
@ Split varchar (10) -- Data Separator
) Returns table
As
-- Select top 8000 id = identity (INT,) into DBO. tb_splitstr -- auxiliary table used by the string splitting function.
-- From syscolumns A, syscolumns B
Return (
Select Col = cast (substring (@ s, ID, charindex (@ split, @ s + @ split, ID)-ID) as varchar (100 ))
From tb_splitstr
Where id <= Len (@ s + 'A ')
And charindex (@ split, @ split + @ s, ID) = ID)
-- Drop table tb_splitstr
Go
/* -- Use the preceding functions to split string field characters in a table.
Declare @ ID int, @ C varchar (50)
Declare K cursor for select * From id_f -- string to be split from the original table (ID int, C varchar (50 ))
Open K
Fetch next from K into @ ID, @ C
While (@ fetch_status = 0)
Begin
Insert into id_yf -- split result table (ID int, C varchar (50 ))
Select @ ID as ID, C. * From f_splitstr (@ C, ',') as C
Fetch next from K into @ ID, @ C
End
Close K
Deallocate K
*/
--------------------------------------------------------------------------
-- String merging and processing code
Declare @ s varchar (300)
Set @ s =''
Select @ s = @ s + C + ','
From [DBO]. [id_h] -- string table to be merged [ID int, C varchar (50)]
Where id <> (select top 1 ID from id_h order by id desc) order by ID
Select @ s = @ s + C from id_h where id = (select top 1 ID from id_h order by id desc)
Select @ s