从Oracle SQL中的文本中提取数值

huangapple
go评论98阅读模式
英文:

Extract value from a text in oracle SQL

问题

我要翻译的内容：

我有一列，其中包含以下内容

  1. (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST="test1.zurich.com")(PORT=1521))
  2. (CONNECT_DATA=(SERVICE_NAME=apps_test1.zurich.com))
  3. (SECURITY=(MY_WALLET_DIRECTORY="/u01/zurich/Fdbb48380hdsvh8/data/zurich_WALLET_DIR")
  4. (SSL_SERVER_DN_MATCH=FALSE)))

现在我只想从完整内容中提取 zurich_WALLET_DIR，如果 MY_WALLET_DIRECTORY 不存在，应该返回 null 值。有人可以帮助我实现吗？

尝试以下但没有成功

SELECT REGEXP_REPLACE(HOST,'(.)(MY_WALLET_DIRECTORY)[[:blank:]]="[[:blank:]]([^)]+)").','\3',1,1,'i')
FROM test;

英文:

I have one column which has the below content

  1. (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST="test1.zurich.com")(PORT=1521))
  2. (CONNECT_DATA=(SERVICE_NAME=apps_test1.zurich.com))
  3. (SECURITY=(MY_WALLET_DIRECTORY="/u01/zurich/Fdbb48380hdsvh8/data/zurich_WALLET_DIR")
  4. (SSL_SERVER_DN_MATCH=FALSE)))

Now I want to extract only zurich_WALLET_DIR from complete content and if MY_WALLET_DIRECTORY doesn't exist it should give me null value. Can someone help me achieve this?

Tried below but no success

  1. SELECT REGEXP_REPLACE(HOST,'(.*)(MY_WALLET_DIRECTORY)[[:blank:]]*="[[:blank:]]*([^\)]+*)\"\).*','',1,1,'i') 
  2.   FROM test;

答案1

得分: 1

以下是您要翻译的代码部分：

  1. SELECT REGEXP_SUBSTR(
  2.          HOST,
  3.          '\(MY_WALLET_DIRECTORY\s*=\s*"(.*?)"\)',
  4.          1,
  5.          1,
  6.          'i',
  7.          1
  8.        ) AS wallet_dir
  9. from   test;

或者，更加易于理解的字符串函数方法，速度比正则表达式要快得多：

  1. SELECT CASE
  2.        WHEN start_pos > 0 AND end_pos > 0
  3.        THEN SUBSTR(host, start_pos + 22, end_pos - start_pos - 22)
  4.        END AS wallet_dir
  5. FROM   (
  6.   SELECT host,
  7.          INSTR(host, '(MY_WALLET_DIRECTORY="', 1) AS start_pos,
  8.          INSTR(host, '")', INSTR(host, '(MY_WALLET_DIRECTORY="', 1) + 22)AS end_pos
  9.   from   test
  10. );

对于示例数据：

  1. CREATE TABLE test (host) AS
  2. SELECT '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST="test1.zurich.com")(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=apps_test1.zurich.com))(SECURITY=(MY_WALLET_DIRECTORY="/u01/zurich/Fdbb48380hdsvh8/data/zurich_WALLET_DIR")(SSL_SERVER_DN_MATCH=FALSE)))' FROM DUAL;

两者的输出结果如下：

WALLET_DIR
/u01/zurich/Fdbb48380hdsvh8/data/zurich_WALLET_DIR

如果您只想获取字符串的最后部分：

  1. SELECT REGEXP_SUBSTR(
  2.          HOST,
  3.          '\(MY_WALLET_DIRECTORY\s*=\s*"[^"]*/(.*?)"\)',
  4.          1,
  5.          1,
  6.          'i',
  7.          1
  8.        ) AS wallet_dir
  9. from   test;

或者：

  1. SELECT SUBSTR(wallet_dir, INSTR(wallet_dir, '/', -1) + 1) AS wallet_dir
  2. FROM   (
  3.   SELECT CASE
  4.          WHEN start_pos > 0 AND end_pos > 0
  5.          THEN SUBSTR(host, start_pos + 22, end_pos - start_pos - 22)
  6.          END AS wallet_dir
  7.   FROM   (
  8.     SELECT host,
  9.            INSTR(host, '(MY_WALLET_DIRECTORY="', 1) AS start_pos,
  10.            INSTR(host, '")', INSTR(host, '(MY_WALLET_DIRECTORY="', 1) + 22)AS end_pos
  11.     from   test
  12.   )
  13. );

这两者的输出结果如下：

WALLET_DIR
zurich_WALLET_DIR
英文:

You can use:

  1. SELECT REGEXP_SUBSTR(
  2.          HOST,
  3.          '\(MY_WALLET_DIRECTORY\s*=\s*"(.*?)"\)',
  4.          1,
  5.          1,
  6.          'i',
  7.          1
  8.        ) AS wallet_dir
  9. from   test;

or, more to type but simple string functions are much faster than regular expressions:

  1. SELECT CASE
  2.        WHEN start_pos > 0 AND end_pos > 0
  3.        THEN SUBSTR(host, start_pos + 22, end_pos - start_pos - 22)
  4.        END AS wallet_dir
  5. FROM   (
  6.   SELECT host,
  7.          INSTR(host, '(MY_WALLET_DIRECTORY="', 1) AS start_pos,
  8.          INSTR(host, '")', INSTR(host, '(MY_WALLET_DIRECTORY="', 1) + 22)AS end_pos
  9.   from   test
  10. );

Which, for the sample data:

  1. CREATE TABLE test (host) AS
  2. SELECT '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST="test1.zurich.com")(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=apps_test1.zurich.com))(SECURITY=(MY_WALLET_DIRECTORY="/u01/zurich/Fdbb48380hdsvh8/data/zurich_WALLET_DIR")(SSL_SERVER_DN_MATCH=FALSE)))' FROM DUAL;

Both output:

WALLET_DIR
/u01/zurich/Fdbb48380hdsvh8/data/zurich_WALLET_DIR

If you just want the last part of the string then:

  1. SELECT REGEXP_SUBSTR(
  2.          HOST,
  3.          '\(MY_WALLET_DIRECTORY\s*=\s*"[^"]*/(.*?)"\)',
  4.          1,
  5.          1,
  6.          'i',
  7.          1
  8.        ) AS wallet_dir
  9. from   test;

or:

  1. SELECT SUBSTR(wallet_dir, INSTR(wallet_dir, '/', -1) + 1) AS wallet_dir
  2. FROM   (
  3.   SELECT CASE
  4.          WHEN start_pos > 0 AND end_pos > 0
  5.          THEN SUBSTR(host, start_pos + 22, end_pos - start_pos - 22)
  6.          END AS wallet_dir
  7.   FROM   (
  8.     SELECT host,
  9.            INSTR(host, '(MY_WALLET_DIRECTORY="', 1) AS start_pos,
  10.            INSTR(host, '")', INSTR(host, '(MY_WALLET_DIRECTORY="', 1) + 22)AS end_pos
  11.     from   test
  12.   )
  13. );

Which both output:

WALLET_DIR
zurich_WALLET_DIR

fiddle

答案2

得分: 1

尝试这个正则表达式：

  1. select regexp_substr(HOST,'MY_WALLET_DIRECTORY=.*\/(.*)"',1,1,NULL,1) as Extracted
  2. from mytable;

结果：

  1. EXTRACTED
  2. zurich_WALLET_DIR

演示在这里

英文:

Try this Regex :

  1. select regexp_substr(HOST,'MY_WALLET_DIRECTORY=.*\/(.*)"\)',1,1,NULL,1) as Extracted
  2. from mytable;

Result :

  1. EXTRACTED
  2. zurich_WALLET_DIR

Demo here

huangapple
  • 本文由 发表于 2023年3月9日 21:07:40
  • 转载请务必保留本文链接：https://go.coder-hub.com/75685046.html
  • oracle
  • sql
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定