A SQL SERVER row is split into multiple rows by a delimiter-stitched field

Database applications are often encountered where a field store connects key-value strings through delimiters, such as the following table in the filehistory structure column: Keywords

We may convert in some business scenarios into the following forms:

First, create a SQL function that splits the stitched string into a table

CREATE FUNCTION [dbo].[uf_splitstringtotable](    @sInputList VARCHAR(MAX)--List of delimited items,@sDelimiter VARCHAR( -)= ',' --delimiter that separates items)RETURNS @List TABLE(itemVARCHAR( -))BEGINDECLARE @sItem VARCHAR(MAX) while CHARINDEX(@sDelimiter,@sInputList,0)<> 0 BEGIN SELECT  @sItem=RTRIM(LTRIM(SUBSTRING(@sInputList,1,CHARINDEX(@sDelimiter,@sInputList,0)-1))),  @sInputList=RTRIM(LTRIM(SUBSTRING(@sInputList,CHARINDEX(@sDelimiter,@sInputList,0)+LEN(@sDelimiter),LEN(@sInputList)))) IF LEN(@sItem)> 0  INSERT  into @List SELECT @sItem ENDIF LEN(@sInputList)> 0 INSERT  into @List SELECT @sInputList --Put The last item inRETURNENDGO

Then, cross apply is used to generate the desired split result from the table filehistory and from this table column: Keywords through a derived table connection to uf_splitstringtotable (filehistory.keywords).

The specific code is as follows:

SELECTt1.[Id], T1.[Keywords], T1.[FileName], T1.[fileextension], T1.[Createduser], T1.[Createdtime], T1.[Importtype], i.item as keyword fromfilehistory T1OUTERAPPLY uf_splitstringtotable (t1. Keywords,',') I

After the code executes successfully, the following results are returned:

In this way, we can finish the results we need.

2015,fighting for Myself,my Dear darling,my family, ~o (∩_∩) o~.

A SQL SERVER row is split into multiple rows by a delimiter-stitched field