Plsql 拆分字符串的最佳方法 [英] Plsql best way to split string

问题描述

从字符串中提取单词的最佳方法是什么?我已经编造了一些东西，因为我找到了很多方法，但没有一个看起来简单".

What is the best way to extract words from a string? I already made something up because I found many ways and none of them looked 'simple'.

让我们假设有一个名为change_opening_hours"的过程.此过程有一个名为v_perioden"的时间范围字符串输入.

Let's assume there is a procedure called 'change_opening_hours'. This procedure has a time-range string input called 'v_perioden'.

这个字符串看起来像:

'10:00-12:00' 或

'10:00-12:00' OR

'10:00-12:00 14:00-16:00' 或

'10:00-12:00 14:00-16:00' OR

'10:00-12:00 14:00-16:00 18:00-22:00' 等

'10:00-12:00 14:00-16:00 18:00-22:00' etc

现在我已经编造了一些东西来从这个输入中提取每个时间段.

Now I already made something up myself to exstract every period of time from this input.

  v_perioden   VARCHAR2(50) := '10:00-12:00 14:00-18:00 22:00-24:00';
  ...
  -- loop though time-periode depeningd
  -- on amount of spaces
  FOR i IN 0..REGEXP_COUNT(v_perioden, ' ') LOOP
    -- first period
    IF i = 0 THEN DBMS_OUTPUT.PUT_LINE(SUBSTR(v_perioden, 0, 11));
    -- second period
    ELSIF i = 1 THEN DBMS_OUTPUT.PUT_LINE(SUBSTR(v_perioden, 13, 11));
    --thirt period
    ELSIF i = 2 THEN DBMS_OUTPUT.PUT_LINE(SUBSTR(v_perioden, 25, 11));
    END IF;
  END LOOP;

输出:

10:00-12:00
14:00-18:00
22:00-24:00

现在这种方式工作正常，但它不是那么有能力.我试图找出如何从空格上的字符串中提取单词，但这并没有奏效.

Now this way is working fine, but it isn't that capable. I tried to find out how to extract words from a string on a space but this wasn't working out tho.

推荐答案

你应该结合使用 SUBSTR 和 INSTR 函数

you should use a combination of SUBSTR and INSTR Function

select  substr('A B C',0,instr('A B C',' ')) from dual  -- A
UNION ALL
select substr( 'A B C',instr('A B C',' ') , instr('A B C',' ',1,2)-1  ) from dual --B
UNION ALL
select substr( 'A B C', instr('A B C',' ',1,2) , instr('A B C',' ') ) from dual  -- C

*用你的字符串替换 A B C

*Replace A B C with your string

这篇关于Plsql 拆分字符串的最佳方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！