解码文本字符串以在联接中使用
我正在尝试从文本字符串中提取数字并将其连接到另一个表。到目前为止，这是我所得到的：
SELECT sect.id,
sect.section_number,
sect.expression,
p.abbreviation
FROM sections sect
JOIN period p ON SUBSTR(sect.expression, 1, (INSTR(sect.expression,'(')-1)) = p.period_number
AND p.schoolid = 73253
AND p.year_id = 20
JOIN courses c ON sect.course_number = c.course_number
WHERE sect.schoolid = 73253
AND sect.termid >= 2000
我阅读了一些其他线程并找出了如何删除数字（它总是出现在左括号之前）。问题是，这仅考虑了 sect.expression 列中三种数据类型中的两种，
9(A) - check
10(A) - check
但不考虑
5-6(A)
5-6(A) 会引发 Oracle 01722 无效数字错误。 有没有办法可以修改 substr... 行，以便对于 5-6(A) 数据类型，它可以获取第一个数字（5）并与之连接？
值得一提的是，我只有对该表的读取权限，因此任何依赖于创建某种辅助表/列的解决方案都将不起作用。
谢谢！
I'm trying to extract the number from a text string and join it to another table. Here's what I have so far:
SELECT sect.id,
sect.section_number,
sect.expression,
p.abbreviation
FROM sections sect
JOIN period p ON SUBSTR(sect.expression, 1, (INSTR(sect.expression,'(')-1)) = p.period_number
AND p.schoolid = 73253
AND p.year_id = 20
JOIN courses c ON sect.course_number = c.course_number
WHERE sect.schoolid = 73253
AND sect.termid >= 2000
I read some other threads and figured out how to strip out the number (which always comes before the left parenthesis). The problem is that this only accounts for two of the three styles of data that live in the sect.expression column-
9(A) - check
10(A) - check
but not
5-6(A)
5-6(A) would kick back an Oracle 01722 invalid number error.
Is there a way I could modify the substr... line so that for the 5-6(A) data type it would grab the first number (the 5) and join off of that?
It's worth mentioning that I only have read rights to this table so any solution that depends on creating some kind of helper table/column won't work.
Thanks!
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
您可以使用 REGEXP_REPLACE
1) 如果您只想提取数字：
2) 如果您想与字符串开头的数字匹配并忽略后面出现的数字：
You can use REGEXP_REPLACE
1) If you want to extract only numbers:
2) If you want to match with the digits in the start of the string and ignore the ones that appear later:
作为 Oracle 10g，您可以使用正则表达式：
不可否认，我提供的正则表达式需要工作 - 它将获取字符串开头的第一个数字。如果您需要更复杂的正则表达式，我推荐此站点：http://www.regular-expressions。信息/tutorial.html
Being Oracle 10g, you could use a regex instead:
Admittedly, the regex I provided needs work - it will get the first number at the start of the string. If you need a more complicated regex, I recommend this site: http://www.regular-expressions.info/tutorial.html