0

I am looking to extract only the Serial No: value (1G20441XC454) from the following query result:

SELECT REGEXP_SUBSTR('Entry: Make: 1, Model: 6, Year: 7, Serial No: 1G20441XC454, Point of Origin: ', '<what would go here>') "REGEXPR_SUBSTR" FROM DUAL

So I need to find the string 'Serial No:' then find all characters up to the comma, then trim the value to remove the leading space. I also have to go on the assumption that not all comma-delimited fields will be present or in the same order. I am thinking this might require nested REGEXP?

Stephen
  • 1
  • 2

1 Answers1

0

Here's one option:

SQL> SELECT
  2      regexp_substr(regexp_substr('Entry: Make: 1, Model: 6, Year: 7, Serial No: 1G20441XC454, Point of Origin: ',
  3                                  'Serial No: \w+'), '\w+$') "REGEXPR_SUBSTR"
  4  FROM dual;

REGEXPR_SUBS
------------
1G20441XC454

SQL>
Littlefoot
  • 131,892
  • 15
  • 35
  • 57
  • That is perfect. Thank you! – Stephen Nov 08 '21 at 17:09
  • 1
    Since the answer resolved your issue kindly accept it. This helps future questioners having similar questions and removes the question from the unanswered queue. Please do not leave successfully answered question in an unanswered state, – Belayer Nov 09 '21 at 19:57