I'm trying to figure out how to extract a specific part of a string (made out of multiple terms) with a select statement in Oracle SQL.
The values in the column look somewhat like '2E WK 12-345-678 TM 13-06-2017', which has a slight variation in format in each row.
Now I want to create a new column that displays only the '123-456-789' part from each row. The question now is: How can you identify this exact format of 3 numbers, hyphen, 3 numbers, hyphen, 3 numbers from each row?
SUBSTR(...) didn't do it for me since the part in question is not always in the same position. Then I tried to apply REGEXP_LIKE(...) but this doesn't return the right values either.
How should I write the SQL statement to do this? Help is very much appreciated.
Example of strings:
2E XX **18-580-0111**
**18-990-0020**: 11.2.11-11.14.19
**65-660-0838** 2015 xxxx core sysxx
**78-140-401** t/m 0019
** = specific part of the string that's needed
Kind regards!
Your question can directly be translated into a regular expression, for regexp_substr():
select col, regexp_substr(col, '[0-9]{2}-[0-9]{3}-[0-9]{3}')