My Sql server comma separated split function is below . How to convert/migrate or write this function in Oracle 12c or 19c.
CREATE FUNCTION [dbo].[fn_Split]
(
@RowData NVARCHAR(MAX),
@Delimeter NVARCHAR(MAX)
)
RETURNS @rtnvalue TABLE
(
ID INT IDENTITY(1,1),
Data NVARCHAR(MAX)
)
AS
BEGIN
DECLARE @Iterator INT
SET @Iterator = 1
DECLARE @FoundIndex INT
SET @FoundIndex = CHARINDEX(@Delimeter,@RowData)
WHILE (@FoundIndex>0)
BEGIN
INSERT INTO @rtnvalue (data)
SELECT
Data = LTRIM(RTRIM(SUBSTRING(@RowData, 1, @FoundIndex - 1)))
SET @RowData = SUBSTRING(@RowData,
@FoundIndex + DATALENGTH(@Delimeter) / 2,
LEN(@RowData))
SET @Iterator = @Iterator + 1
SET @FoundIndex = CHARINDEX(@Delimeter, @RowData)
END
INSERT INTO @rtnvalue (Data)
SELECT Data = LTRIM(RTRIM(@RowData))
RETURN
END
Example:
select * from dbo.fn_split('Khan,Donald,Arnold',',')
Output:
ID Data
1 Khan
2 Donald
3 Arnold
Please help me.
Thanks