Function to get the value from a key value pair by using the key when the key value pairs are stored in a single column

Question

Imagine I had a table with the following structure in Oracle database.

ID  Name    Attributes
1   Rooney  <Foot=left>, <height=5>, <country=England>
2   Ronaldo <Foot=Right>, <height=6>, <country=Portugal> 

I want to create a function in which can return the value of a specific attribute when I pass in the ID, Name and Key of the attribute I need. Is that possible.

The query inside the function I was trying to use to determine the foot of the player Rooney is something like this.

       SELECT Attributes
       AS tmpVar
       FROM Players 
       WHERE id = 1 AND Name = 'Rooney' and Attributes like '%Foot%';  

Obviously it will give all the attributes but I just want his foot attribute. This is my first question here so please excuse If I made any novice mistakes and advice on how to ask better questions.

Answer 1

you may use REGEXP_SUBSTR

(.+?) captures the value, using non-greedy match, retrieved as 1 (last argument)

select REGEXP_SUBSTR(Attributes,'<Foot=(.+?)>',1,1,NULL,1) as Foot 
FROM Players 

Demo