从 Oracle 中的给定字符串中提取特定字符串 [英] To extract the specific strings from the given string in Oracle

问题描述

表达式 - BR65437812-909@-@BR12340000-990

需要提取给定的表达式并在列中更新，例如a = BR12340000, b = 990

Need to extract the given expression and update in columns like a = BR12340000, b = 990

推荐答案

select 
  SUBSTR(s, 1, INSTR(s, '-') - 1) as a, 
  SUBSTR(s, INSTR(s, '-', -1) + 1) as b 
from 
  (select 'BR65437812-909@-@BR12340000-990' as s from dual)

使用 SUBSTR(string, start, length) 我们有以下参数:

Using SUBSTR(string, start, length) we have the following arguments:

对于 A:

• 要搜索的字符串
• 1 作为 start 和
• (index_of_the_first_hyphen - 1) 作为 length.INSTR(string, searchfor) 给我们第一个连字符的索引

• the string to search
• 1 as the start and
• (index_of_the_first_hyphen - 1) as the length. INSTR(string, searchfor) gives us the index of the first hyphen

对于 B:

使用 SUBSTR(string, start) 我们有参数:

• 要搜索的字符串
• (index_of_last_hyphen + 1) - 这次我们使用额外的 INSTR(string, searchfor, startindex) 参数 startindex 并将其设置为 -1；这使它从字符串的末尾搜索并向后工作，为我们提供最后一个连字符的索引

• the string to search
• the (index_of_last_hyphen + 1) - this time we use the extra INSTR(string, searchfor, startindex) argument startindex and set it to -1; this makes it search from the end of the string and work backwards, giving us the index of the last hyphen

我们不需要长度参数 - 没有长度的 SUBSTR 将字符串的其余部分返回到末尾

We don't need a length argument - SUBSTR without length returns the rest of the string to the end

需要注意的是，起始索引为 -1 的 INSTR 确实会向后搜索，但它总是从字符串的开头而不是结尾返回索引.

It's important to note that INSTR with a start index of -1 does search backwards but it always returns the index from the start of the string, not the end.

INSTR('dddde', 'd', -1)  
       12345            -- returns 4, because d is 4 from the start
       54321            -- it does not return 2, even though d is 2 from the "start" when searching backwards

这篇关于从 Oracle 中的给定字符串中提取特定字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！