PLSQL show digits from end of the string

Question

I have the following problem. There is a String:

There is something 2015.06.06. in the air 1234567 242424 2015.06.07. 12125235

I need to show only just the last date from this string: 2015.06.07. I tried with regexp_substr with insrt but it doesn't work. So this is just test, and if I can solve this after it with this solution I should use it for a CLOB query where there are multiple date, and I need only the last one. I know there is regexp_count, and it is help to solve this, but the database what I use is Oracle 10g so it wont work.

Can somebody help me?

Answer 1

The key to find the solution of this problem is the idea of reversing the words in the string presented in this answer.

Here is the possible solution:

WITH words AS
(
SELECT regexp_substr(str, '[^[:space:]]+', 1, LEVEL) word, 
        rownum rn
   FROM (SELECT 'There is something 2015.06.06. in the air 1234567 242424 2015.06.07. 2015.06.08 2015.06.17. 2015.07.01. 12345678999 12125235' str
           FROM dual) tab
CONNECT BY LEVEL <= LENGTH(str) - LENGTH(REPLACE(str, ' ')) + 1
)
, words_reversed AS 
(
SELECT *
  FROM words
 ORDER BY rn DESC
)
SELECT regexp_substr(word, '\d{4}\.\d{2}\.\d{2}', 1, 1)
  FROM words_reversed
 WHERE regexp_like(word, '\d{4}\.\d{2}\.\d{2}')
   AND rownum = 1;

Answer 2

From the documentation on regexp_substr, I see one problem immediately:

The . (period) matches any character. You need to escape those with a backslash: \. in order to match only a period character.

For reference, I am linking this post which appears to be the approach you are taking with substr and instr.

Relevant documentation from Oracle:

INSTR(string , substring [, position [, occurrence]])

When position is negative, then INSTR counts and searches backward from the end of string. The default value of position is 1, which means that the function begins searching at the beginning of string.

The problem here is that your regular expression only returns a single value, as explained here, so you will be giving the instr function the appropriate match in the case of multiple dates.

Now, because of this limitation, I recommend using the approach that was proposed in this question, namely reverse the entire string (and your regular expression, i.e. \d{2}\.\d{2}\.\d{4}) and then the first match will be the 'last match'. Then, perform another string reversal to get the original date format.

Maybe this isn't the best solution, but it should work.

Answer 3

There are three different PL/SQL functions that will get you there.

1. The INSTR function will identify where the first "period" in the date string appears.
2. SUBSTR applied to the entire string using the value from (1) as the start point
3. TO_DATE for a specific date mask: YYYY.MM.DD will convert the result from (2) into a Oracle date time type.

To make this work in procedural code, the standard blocks apply:

 DECLARE
    v_position pls_integer;
    ... other variables
 BEGIN
    sql code and function calls;

 END

SQL Fiddle

Oracle 11g R2 Schema Setup:

CREATE TABLE finddate
    (column1 varchar2(11), column2 varchar2(39))
;

INSERT ALL 
    INTO finddate (column1, column2)
         VALUES ('row1', '1234567 242424 2015.06.07. 12125235')
    INTO finddate (column1, column2)
         VALUES ('string2', '1234567 242424 2015.06.07. 12125235')
SELECT * FROM dual
;

Query 1:

select instr(column2,'.',1) from finddate
where column1 = 'string2'

select substr(column2,(20-4),10) from finddate

select to_date('2015.06.07','YYYY.MM.DD') from finddate

Results:

| TO_DATE('2015.06.07','YYYY.MM.DD') |
|------------------------------------|
|             June, 07 2015 00:00:00 |
|             June, 07 2015 00:00:00 |

Answer 4

Here's a way using regexp_replace() that should work with 10g, assuming the format of the lines will be the same:

with tbl(col_string) as
(
  select 'There is something 2015.06.06. in the air 1234567 242424 2015.06.07. 12125235' 
  from dual
)
select regexp_replace(col_string, '^.*(\d{4}\.\d{2}\.\d{2})\. \d*$', '\1')
from tbl;

The regex can be read as:

^   - Match the start of the line
.   - followed by any character
*   - followed by 0 or more of the previous character (which is any character)
(   - Start a remembered group
\d{4}\.\d{2}\.\d{2} - 4 digits followed by a literal period followed by 2 digits, etc
)   - End the first remembered group
\.  - followed by a literal period
    - followed by a space
\d* - followed by any number of digits
$   - followed by the end of the line

regexp_replace then replaces all that with the first remembered group (\1).

Basically describe the whole line as a regular expression, group around what you want to return. You will most likely need to tweak the regex for the end of the line if it could be other characters than digits but this should give you an idea.

For the sake of argument this works too ONLY IF there are 2 occurrences of the date pattern:

with tbl(col_string) as
(
  select 'There is something 2015.06.06. in the air 1234567 242424 2015.06.07. 12125235' from dual
)
select regexp_substr(col_string, '\d{4}\.\d{2}\.\d{2}', 1, 2)
from tbl;

returns the second occurrence of the pattern. I expect the above regexp_replace more accurately describes the solution.