SQL Server 2016

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE Fruit (
    FruitID int IDENTITY(1,1) PRIMARY KEY,
    Name varchar(255),
    Data text,
    Description varchar(255)
)

INSERT INTO Fruit (Name, Data, Description)
VALUES ('Banana', 'key 1=value 1' + CHAR(13)+CHAR(10) + 'key 2=value 2' + CHAR(13)+CHAR(10) + 'key 3=value 3' + CHAR(13)+CHAR(10) + 'key 4=value 4' + CHAR(13)+CHAR(10) + 'key 5=value 5','Delicious');

INSERT INTO Fruit (Name, Data, Description)
VALUES ('Pear', 'key 1=value 1' + CHAR(13)+CHAR(10) + 'key 2=value 2' + CHAR(13)+CHAR(10) + 'key 3=value 3' + CHAR(13)+CHAR(10) + 'key 4=value 4' + CHAR(13)+CHAR(10) + 'key 5=value 5','Rotton');

INSERT INTO Fruit (Name, Data, Description)
VALUES ('Kiwi', 'key 1=value 1' + CHAR(13)+CHAR(10) + 'key 2=value 2' + CHAR(13)+CHAR(10) + 'key 3=value 3' + CHAR(13)+CHAR(10) + 'key 4=value 4' + CHAR(13)+CHAR(10) + 'key 5=value 5','Okay');

SELECT * FROM Fruit


FruitID
Name
Data
Description


1
Banana
key 1=value 1
key 2=value 2
key 3=value 3
key 4=value 4
key 5=value 5
Delicious

2
Pear
key 1=value 1
key 2=value 2
key 3=value 3
key 4=value 4
key 5=value 5
Rotton

3
Kiwi
key 1=value 1
key 2=value 2
key 3=value 3
key 4=value 4
key 5=value 5
Okay


CREATE FUNCTION fnGetKey(@Data text, @Key varchar(20))
RETURNS varchar(100)
AS
BEGIN

  RETURN
  (
  SELECT 
      SUBSTRING (
                  @Data,
                  /* Position of first '=' after key + 1 */
                  CHARINDEX('=', @Data, PATINDEX('%' + @key + '%', @Data)) + 1,
                  /* Lenght, Position of first chr(13) after key less previuos value - 1 */
                  (CHARINDEX(CHAR(13), @Data, PATINDEX('%' + @key + '%', @Data)) 
                  - 
                  CHARINDEX('=', @Data, PATINDEX('%' + @key + '%', @Data))) - 1
                )
  )
  
END
SELECT
    FruitID, Name, Description,
    dbo.fnGetKey([Data], 'key 2') as [key 2],
    dbo.fnGetKey([Data], 'key 4') as [key 4]
FROM
    [Fruit];


FruitID
Name
Description
key 2
key 4


1
Banana
Delicious
value 2
value 4

2
Pear
Rotton
value 2
value 4

3
Kiwi
Okay
value 2
value 4

FruitID	Name	Data	Description
1	Banana	key 1=value 1 key 2=value 2 key 3=value 3 key 4=value 4 key 5=value 5	Delicious
2	Pear	key 1=value 1 key 2=value 2 key 3=value 3 key 4=value 4 key 5=value 5	Rotton
3	Kiwi	key 1=value 1 key 2=value 2 key 3=value 3 key 4=value 4 key 5=value 5	Okay

FruitID	Name	Description	key 2	key 4
1	Banana	Delicious	value 2	value 4
2	Pear	Rotton	value 2	value 4
3	Kiwi	Okay	value 2	value 4