Press, cutting a column values for multiple columns-CodePudding

SQL SERVER query want
Id resourceid
1
22, 3,
3 1
3 2
3, 4,
3 5
4 1
.

Hope the great god,

CodePudding user response:

 CREATE FUNCTION dbo. F_splite 
(
@ s VARCHAR (8000), - 
 string to be split@ the split VARCHAR (10) -- data separator 
) 
RETURNS the @ re TABLE (id INT, col VARCHAR (100)) 
AS 
The BEGIN 
DECLARE @ splitlen INT 
DECLARE @ INT I=1 
The SET @ splitlen=LEN (@ the split + 'a') - 2 
WHILE CHARINDEX (@ the split, @ s) & gt; 0 
The BEGIN 
INSERT the @ re 
VALUES (@ I, LEFT (@ s, CHARINDEX (@ the split, @ s) - 1)) 
The SET @ s=STUFF (@ s, 1, CHARINDEX (@ the split, @ s) + @ splitlen, ' ') 
The SET @ I=@ I + 1 
END 
INSERT the @ re 
VALUES (@ I, @ s) 
RETURN 
END 
GO

The test data

 -If not object_id (N 'Tempdb for.. # T ') is null 
Drop table # T 
Go 
The Create table # T (int [id], [resourceid] nvarchar (29)) 
Insert # T 
Select 1, N '1' union all 
Select 2, N '3' union all 
Select 3, N '1.2.3.4.5' 
Go 
- the end of the test data 
The SELECT # T.i d, 
Tc ol 
The FROM # T 
CROSS the APPLY (SELECT * FROM dbo. F_splite ([resourceid], '. ')) t;

CodePudding user response:

Can directly select out the results, and not to walk the function seen a similar approach, because in oracle,

CodePudding user response:

reference JAVAccxx reply: 3/f

can directly select out the results, and not to walk the function seen a similar approach, because in oracle,

Essentially a written as very complicated, can use recursion, but separator, is to define the location of the length of the partition,