Decompose a string into a table (multiple rows, multiple columns) table-valued functions [StrSplit_Tab]
Use a string to describe the contents of a table and use this table-valued function to restore it to a table if needed.
Because it is impossible to predict the number of columns and column names of the table, [column conversion] is performed when needed.
This function, like the [StrSplit] function, can also decompose a string into a single column, as long as the argument @ ColumnStr=''
Of course, you can also store the column name of the table in a string.
Here is the SQL code:
FUNCTION [dbo]. [StrSplit_Tab] (
@ String nvarchar (MAX)-- string to be split
, @ RowStr nvarchar (10)-line separator
, @ ColumnStr nvarchar (10)-column separator
, @ NullItem int=0-- whether to keep the location number of [empty element] [item]. Not reserved for 0, not reserved for 0.
) returns @ Tab table (
[Row] int-- [Row]: line number (1, 2, 3, 4, 5.)
, [Column] nvarchar (20)-- [Column]: column number (F1 Magi F2 Magi F3.)
, [item] nvarchar (MAX)-- [item]: cell element
As
Begin
IF ISNULL (@ RowStr,'') =''begin-- No [line separator @ RowStr], return the original text
Insert into @ Tab ([Row], [Column], [item]) VALUES (1)
RETURN
End
SET @ NullItem=ISNULL (@ NullItem,0)
Declare @ Ritem nvarchar (MAX);-- text
Declare @ begin as int=1,@end as int;-- intercepts the text, starting and ending position
Declare @ RL AS int=LEN (@ RowStr);-- Separator length
Declare @ R AS int=0;-- Line number
-- decompose rows
Set @ end=charindex (@ RowStr,@String,@begin)
While (@ end0)
Begin
Set @ Ritem = substring (@ String,@begin,@end-@begin)
IF @ NullItem0 SET @ R=@R+1
IF len (@ Ritem) > 0 begin
-- decompose column-- self-call
IF @ NullItem=0 SET @ R=@R+1
Insert into @ Tab ([Row], [Column], [item])
SELECT @ R (Row) as nvarchar (50), [item]
FROM [dbo]. [StrSplit_Tab] (@ Ritem,@ColumnStr,'')
End
Set @ begin=@end+@RL
Set @ end=charindex (@ RowStr,@String,@begin)
End
-- end text processing
Set @ Ritem = substring (@ String,@begin,len (@ String) + 1-@begin)
IF len (@ Ritem) > 0 begin
SET @ R=@R+1
-- decompose column-- self-call
Insert into @ Tab ([Row], [Column], [item])
SELECT @ R (Row) as nvarchar (50), [item]
FROM [dbo]. [StrSplit_Tab] (@ Ritem,@ColumnStr,'')
End
Return
End
-- = use example =
Select [Row] as' Row'
Max ([F1]) as' F1'
Max ([F2]) as' F2'
Max ([F3]) as' F3'
From [dbo]. [StrSplit_Tab] (
: well-off: 342222199506186034; Yang Lan: 342222197909226046; Xiang Feng: 42282519890310110X; Yang Yu: 5102271975102765Ten
,';'
,':'
, 1)
Pivot (
Max ([item]) for [Column]
In (F1, F2, F3)
) as B
Group by [Row]
Order by [Row] asc
