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.
我试图弄清楚如何使用Oracle SQL中的select语句提取字符串的特定部分(由多个术语组成)。
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.
该列中的值看起来有点像'2E WK 12-345-678 TM 13-06-2017',其中每行的格式略有不同。
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?
现在我想创建一个新列,每行只显示'123-456-789'部分。现在的问题是:如何识别每行3个数字,连字符,3个数字,连字符,3个数字的确切格式?
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.
SUBSTR(...)没有为我做,因为有问题的部分并不总是在同一个位置。然后我尝试应用REGEXP_LIKE(...),但这也没有返回正确的值。
How should I write the SQL statement to do this? Help is very much appreciated.
我该如何编写SQL语句来执行此操作?非常感谢帮助。
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!
1 个解决方案
#1
0
Your question can directly be translated into a regular expression, for regexp_substr():
对于regexp_substr(),您的问题可以直接转换为正则表达式:
select col, regexp_substr(col, '[0-9]{2}-[0-9]{3}-[0-9]{3}')
#1
0
Your question can directly be translated into a regular expression, for regexp_substr():
对于regexp_substr(),您的问题可以直接转换为正则表达式:
select col, regexp_substr(col, '[0-9]{2}-[0-9]{3}-[0-9]{3}')