Oracle SQL 12.1 removing a changing substring from a string

Question

I have the following example

05.04.2018 at 11:10:37 AEST

My goal is to remove all alpha chars from the string. The expected result is to remove the ' at' and ' AEST' sub-strings:

Note: There should be only one space between the date and the time. There should be no space at the end of the string

05.04.2018 11:10:37

The 'AEST' sub-string is a timezone and can change.

This is my current SQL query:

select SUBSTR(REGEXP_REPLACE(REGEXP_REPLACE('05.04.2018 at 11:10:37 AEST',' at',''), ' EST| AEDT| AEST', ''),1) from dual;

I'm looking to enhance my query (preferably using regex) so I will not have to specify explicitly all potential values for timezone (as currently being done in the query)

Thanks

Wiktor Stribiżew · Accepted Answer · 2018-04-05 11:38:03Z

3

You may use \s*[a-zA-Z]+ / \s*[[:alpha:]]+ regex:

select REGEXP_REPLACE('05.04.2018 at 11:10:37 AEST','\s*[a-zA-Z]+','') as Result from dual

The pattern matches

\s* - 0+ whitespace chars
[a-zA-Z]+ - 1+ ASCII letters ([[:alpha:]]+ will match any letters).

See an online Oracle demo. Output:

answered Apr 5, 2018 at 11:38

Wiktor Stribiżew

631k41 gold badges502 silver badges632 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

greennewbie Over a year ago

PERFECT. Thanks!

Littlefoot · Accepted Answer · 2018-04-05 11:38:11Z

3

Something like this?

SQL> with test as (select '05.04.2018 at 11:10:37 AEST' col from dual)
  2  select regexp_replace(col, '\s*[[:alpha:]]+') result
  3  from test;

RESULT
-------------------
05.04.2018 11:10:37

SQL>

answered Apr 5, 2018 at 11:38

Littlefoot

144k15 gold badges41 silver badges65 bronze badges

Comments

Gordon Linoff · Accepted Answer · 2018-04-05 11:41:46Z

1

You can use:

select trim(regexp_replace(col, '[a-zA-Z]', ''))

I assume you want to remove the final space as well.

answered Apr 5, 2018 at 11:41

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

1 Comment

greennewbie Over a year ago

Thanks. That approach leaves double space between the date and time

miracle173 · Accepted Answer · 2018-04-05 12:04:10Z

1

Keep it simple! Why not without regexp? The date part and the time part are always at the same position.

select substr(col,1,10)  -- the date part
    ||' '||              -- the blank
    substr(col,15,8)     -- the time part
from tab;

e.g.

SQL> select substr(col,1,10)  
        ||' '||
        substr(col,15,8)  "date+time"   
     from (
        select '05.04.2018 at 11:10:37 AEST' col
            from dual) tab; 

date+time
-------------------
05.04.2018 11:10:37

edited Apr 5, 2018 at 12:04

answered Apr 5, 2018 at 11:45

miracle173

1,97116 silver badges34 bronze badges

Collectives™ on Stack Overflow

Oracle SQL 12.1 removing a changing substring from a string

4 Answers 4

1 Comment

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related