I want to create function which returns a table with two columns extracted from an input string.
As an input, the function gets a string like:
'@Name=John;@Secondname=Kowalsky;@[email protected];'
The function is supposed to create from this string two columns - x, y.
X for @value and Y for value after =.
I created this:
Create FUNCTION TwoColumnsFromString (
@ReplaceString varchar(max)
)
Returns @temptable TABLE (x varchar(max), y varchar(max))
AS
BEGIN
DECLARE @value1 varchar(max)
WHILE (LEN(@value1)) > 0
BEGIN
SELECT @value1 = (Select @ReplaceString)
INSERT INTO @temptable (x,y)
VALUES ((SUBSTRING(@ReplaceString, CHARINDEX('@', @ReplaceString), CHARINDEX('=',@ReplaceString) -CHARINDEX('@',@ReplaceString))),
(SUBSTRING(@ReplaceString, CHARINDEX('=', @ReplaceString)+1, CHARINDEX(';',@ReplaceString) -CHARINDEX('=',@ReplaceString)-1)))
SET @value1 = REPLACE(@value1, SUBSTRING(@value1, 1, CHARINDEX(';', @value1)), '')
END
RETURN
END
GO
SELECT *
FROM TwoColumnsFromString('@Name=John;@Secondname=Kowalsky;@[email protected];')
But that returns an empty table. What am I doing wrong here?
You lost all your good work from your previous question :)
@value1 its starting value outside your loop@value1Your function body should be
BEGIN
-- Set starting value outside the loop
DECLARE @value1 VARCHAR(max) = @ReplaceString;
WHILE (LEN(@value1)) > 0
BEGIN
-- We are now using @value1 not @ReplaceString
INSERT INTO @temptable (x,y)
VALUES ((SUBSTRING(@value1, CHARINDEX('@', @value1), CHARINDEX('=',@value1) -CHARINDEX('@',@value1))),
(SUBSTRING(@value1, CHARINDEX('=', @value1)+1, CHARINDEX(';',@value1) -CHARINDEX('=',@value1)-1)));
SET @value1 = REPLACE(@value1, SUBSTRING(@value1, 1, CHARINDEX(';', @value1)), '');
END
RETURN;
END
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments