Its not an assignment.I have come up with the below solution.
CREATE FUNCTION [dbo].[StringToTable]
(
@inputString nvarchar(max),
@separator char (1),
@tokens int
)
RETURNS nvarchar(4000)--@ResultTable TABLE ( [String] nvarchar(max) )
AS
BEGIN
DECLARE @ResultTable TABLE ( [String] nvarchar(max) )
DECLARE @ResultVar nvarchar(4000)
DECLARE @stringToInsert nvarchar (max)
DECLARE @cnt int
WHILE LEN(@inputString) > 0
BEGIN
SET @StringToInsert = LEFT(
@inputString,
ISNULL(NULLIF(CHARINDEX(@separator, @inputString) - 1, -1),
LEN(@inputString)
)
)
SET @InputString = SUBSTRING(@InputString,
ISNULL
(NULLIF
(CHARINDEX(@separator, @InputString),
0),
LEN(@InputString)) + 1,
LEN(@InputString))
INSERT INTO @ResultTable
(
[String]
)
VALUES
(
@StringToInsert
)
END
SELECT @cnt = count(*) from @ResultTable
If @cnt>= @tokens
BEGIN
SELECT @ResultVar = STUFF((SELECT top (@tokens) '.' + [String] FROM @ResultTable
FOR XML PATH('')),1, 1, '') ;
END
ELSE
BEGIN
SELECT @ResultVar = NULL
END
RETURN @ResultVar;
END
----------------------
You can execute the above function as
select [dbo].[StringToTable]('1.1.2.3','.',2) will return '1.1'
I am trying to modify the function without the use of @ResultTable TABLE
Paramater.