I am trying to parse the yaml string to a column in SQL.

I have a yaml string in one of the columns like

country_cd:GB
postal_town_city:London
zip:CT11 A1Cr

I have to write a query to pass the key and get a value. When I pass 'country_cd', I want to receive 'GB'.

I have tried the below script but it is returning 'country_cd:G'. What am I doing incorrectly ?

 select trim(regexp_substr('---
 country_cd:GB
 postal_town_city:London
 zip:CT11 A1Cr', '\n?'||'postal_town_city'||'*[:=] *(. ?) *\n?', 1, 1, 'i')) from dual ; 

CodePudding user response：

You can use REGEXP_SUBSTR with the m match parameter to parse the string as separate lines:

SELECT REGEXP_SUBSTR(yaml, '^country_cd:(.*)$', 1, 1, 'm', 1) AS value
FROM   table_name;

Which, for the sample data:

CREATE TABLE table_name (yaml) AS
SELECT 'country_cd:GB
postal_town_city:London
zip:CT11 A1Cr' FROM DUAL;

Outputs:

VALUE
GB

or, you can split the string using simple string functions (which are often quicker than using regular expressions) and then filter for the correct key:

WITH lines (yaml, line_start, separator_pos, line_end) AS (
  SELECT yaml,
         1,
         INSTR(yaml, ':', 1),
         INSTR(yaml, CHR(10), 1)
  FROM   table_name
UNION ALL
  SELECT yaml,
         line_end   1,
         INSTR(yaml, ':', line_end   1),
         INSTR(yaml, CHR(10), line_end   1)
  FROM   lines
  WHERE  line_end > 0
)
SELECT SUBSTR(yaml, line_start, separator_pos - line_start) AS key,
       CASE line_end
       WHEN 0
       THEN SUBSTR(yaml, separator_pos   1)
       ELSE SUBSTR(yaml, separator_pos   1, line_end - separator_pos)
       END AS value
FROM   lines
WHERE  SUBSTR(yaml, line_start, separator_pos - line_start) = 'country_cd'

Which outputs:

KEY	VALUE
country_cd	GB

db<>fiddle here

CodePudding user response：

Using REGEXP_SUBSTR with a capture group, we can try:

SELECT yaml,
       REGEXP_SUBSTR(yaml_col, '(^|\s)country_cd:(\S )', 1, 1, NULL, 2) AS country_cd
FROM yourTable;

Here is regex demo.