UFN_STRINGSPLITTER
Splits a string by the given delimiter and return a table of all the segments.
Return
| Return Type |
|---|
| table |
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @delimited | nvarchar(max) | IN | |
| @delimiter | nvarchar(100) | IN |
Definition
Copy
CREATE FUNCTION dbo.UFN_STRINGSPLITTER
(
@delimited nvarchar(max),
@delimiter nvarchar(100)
) RETURNS @output TABLE
(
ID int identity(1,1),
VALUE nvarchar(max)
)
AS
BEGIN
declare @xml xml
set @xml = N'<root><r>' + replace(@delimited,@delimiter,'</r><r>') + '</r></root>'
insert into @output(VALUE)
select
r.value('.','varchar(max)') as item
from @xml.nodes('//root/r') as records(r)
RETURN
END