how to create scalar valued functions for string split

please help me. i am new to scalar valued valued function

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

8y ago
The above one is table value function

when i create that below function it is created under table valued functions

i want scalar valued functions for string split.

for that how to do. please let me know.
0
sudipta sanyal8y ago
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[fn_split]
(
@Input NVARCHAR(MAX),
@Character CHAR(1)
)
RETURNS @Output TABLE (
value NVARCHAR(1000)
)
AS
BEGIN
DECLARE @StartIndex INT, @EndIndex INT

SET @StartIndex = 1
IF SUBSTRING(@Input, LEN(@Input) - 1, LEN(@Input)) <> @Character
BEGIN
SET @Input = @Input + @Character
END

WHILE CHARINDEX(@Character, @Input) > 0
BEGIN
SET @EndIndex = CHARINDEX(@Character, @Input)

INSERT INTO @Output(value)
SELECT SUBSTRING(@Input, @StartIndex, @EndIndex - 1)

SET @Input = SUBSTRING(@Input, @EndIndex + 1, LEN(@Input))
END

RETURN
END

GO
-----------------------
SELECT * FROM dbo.[fn_split]('abc,xyz,kbc',',')
0
Sekhar Babu8y ago
You can use string_split function
SELECT * FROM STRING_SPLIT('abc,xyz,kbc',',')
0