Database Table field data segmentation

Database Table field data segmentation

Sometimes, to reduce the number of stored records, multiple records may be merged into one display. In this case, the other fields recorded in the table are the same, and only one field is changed. For example, in personnel management, the IDS of related persons in the Department are placed in a field of a record, separated by commas. The requirement is to split a record into multiple records by ID field.

Create table [dbo]. [Table_Dept] ([DEPT_CODE] [int] NULL, [content] [nvarchar] (50) NULL, [A0188s] [nvarchar] (max) NULL) ON [PRIMARY] GOinsert into Table_Dept select 1000, 'General Department ', '000000' union all select 350,688,258, 'general manager', '2, 3, 4, 1001'

The result of querying this table is as follows. DEPT_CODE is the department code, content is the department name, And A0188s is the relevant person.

Now you need to separate the IDs in A0188s into multiple display entries. Use a custom string segmentation function. The segmentation function script is as follows:

CREATE   FUNCTION [dbo].[Split]   (   @c VARCHAR(MAX) ,   @split VARCHAR(50)   )   RETURNS @t TABLE ( col VARCHAR(50) )   AS  BEGIN      WHILE ( CHARINDEX(@split, @c) <> 0 )           BEGIN              INSERT  @t( col )               VALUES  ( SUBSTRING(@c, 1, CHARINDEX(@split, @c) - 1) )               SET @c = STUFF(@c, 1, CHARINDEX(@split, @c), '')           END      INSERT  @t( col ) VALUES  ( @c )       RETURN  END

However, this function can only process a single record. Here, we consider traversing the original table with a cursor, decomposing them one by one, and then storing them in the temporary table.

IF object_id('tempdb..#TEMPTB1') is not nullBEGINdrop table #TEMPTB1ENDCREATE table #TEMPTB1(    [DEPT_CODE] [int] NULL,    [content] [nvarchar](50) NULL,    [A0188s] [nvarchar](max) NULL)IF object_id('tempdb..#TEMPTB2') is not nullBEGINdrop table #TEMPTB2ENDCREATE table #TEMPTB2(    [pid] [nvarchar](max) NULL)declare @DEPT_CODE intdeclare @content varchar(50)declare @A0188s varchar(max)exec('declare my_cursor1 cursor for select * from [Table_Dept]')open my_cursor1declare @id1 sysnamedeclare @id2 sysnamedeclare @id3 sysnamefetch next from my_cursor1 into @id1,@id2,@id3while(@@fetch_status= 0)beginset @DEPT_CODE =convert(int,@id1)set @content =convert(varchar(50),@id2)set @A0188s =convert(varchar(max),@id3)truncate table #TEMPTB2     insert into #TEMPTB2 select * from Split(@A0188s,',')     insert into #TEMPTB1 select @DEPT_CODE,@content,pid from #TEMPTB2fetch next from my_cursor1 into @id1,@id2,@id3endclose my_cursor1deallocate my_cursor1select * from #TEMPTB1

Get the final result

Database Field Segmentation

Let's take a closer look. This is simple:

1 ==>: this is the marker at the beginning of the first line.

<+ _ +>: The delimiter between a record and a record.

<_ @>: The delimiter between a field and a field.

Then, if it is in C #, split the program.
If you want to split the database, write a stored procedure.

How to separate commas in fields in the database

It depends on whether your database syntax contains split or similar splitting functions. If the database does not support this function, check whether your foreground programming language has this function, if the front-end language does not support this function, you can write such a splitting function on your own.