Introduction: This function will split the string given by the
character given in SQL.
Code:
create FUNCTION [dbo].[string_split]
(
@string
NVARCHAR(MAX),
@split_character CHAR(1)
)
RETURNS @result TABLE (
Item NVARCHAR(1000)
)
AS
BEGIN
DECLARE @StartIndex INT, @EndIndex INT
SET @StartIndex = 1
IF SUBSTRING(@string, LEN(@string) - 1, LEN(@string)) <> @split_character
BEGIN
SET @string = @string
+ @split_character
END
WHILE CHARINDEX(@split_character,
@string) > 0
BEGIN
SET @EndIndex = CHARINDEX(@split_character, @string)
INSERT INTO @result(Item)
SELECT rtrim(ltrim(SUBSTRING(@string, @StartIndex, @EndIndex - 1)))
SET @string = SUBSTRING(@string, @EndIndex + 1, LEN(@string))
END
RETURN
END
To excute function:
select item from
[string_split] ('Ram, Shyam', ',')
