CODEWITHSUNDEEP
sqlregexoracle-database
greennewbie
greennewbie

Reputation: 33

Oracle SQL 12.1 removing a changing substring from a string

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

Upvotes: 3

Views: 80

Answers (4)

miracle173
miracle173

Reputation: 1973

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

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269853

You can use:

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

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

Upvotes: 1

Littlefoot
Littlefoot

Reputation: 142733

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>

Upvotes: 3

Wiktor Stribiżew
Wiktor Stribiżew

Reputation: 626861

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:

enter image description here

Upvotes: 3

Related Questions