oracle regex_replace to keep digit and backslash

oracle regexp_replace examples
oracle regexp_replace special characters
oracle regexp_replace multiple patterns
oracle regexp_replace multiple spaces with single space
oracle regexp_like digit
oracle replace multiple arguments
regular expression in oracle 12c
oracle regexp_replace alphanumeric

I have excel file that contains some junk data in date field.

the date format is in 1/2/2015 format. I'm trying to load that file into a stage table in varchar field and apply regex replace function to clean up the bad data before loading to main table

can i somebody provide me the suitable experssion for this

create table A
(
bad_date varchar2(4000)
);

insert into A
( bad_date)
values ('1/2/2005');
insert into A
( bad_date)
values ('indep');
insert into A
( bad_date)
values ('/');

commit;


 create table B
    (
    good_date date
    );

I want to use regex function to cleanse the data which is not in the date pattern. Thanks for your help!

Use ^[0-9]{1,2}/[0-9]{1,2}/[0-9]{4} pattern for regexp_like conforming your date format.

Use the following insert statement to get clean date data :

insert into B
select * from
( select to_date(bad_date,
           case when 
             regexp_like(bad_date,'^[0-9]{1,2}/[0-9]{1,2}/[0-9]{4}') then 
             'DD/MM/YYYY'
           end) dateConverted          
    from A)
where dateConverted is not null;    

SQL Fiddle Demo

Using Regular Expressions in Oracle Database, The built-in SQL and PL/SQL regular expression functions and conditions Use a backslash (\) to search for a character that is normally treated as a metacharacter. can search for special characters such as digits or punctuation characters. RegEx Replace The RegEx Replace processor provides a way to perform advanced text replacements by matching String or String Array attributes to a regular expression, and replacing the matching value with a specific value, or with a value derived from the matched text - for example replacing the whole of a string that matched a regular expression with only the first group in the expression.

You can come close with something like:

select (case when regexp(bad_date, '^[0-1]?[0-9]/[0-3]?[0-9]/[0-9]{4}$')
             then to_date(bad_date, 'MM/DD/YYYY'
        end) as converted_date

Oracle / PLSQL: REGEXP_REPLACE Function, 10g, will allow you to replace a sequence of characters in a string with another set of characters using regular expression pattern matching. oracle regex_replace to keep digit and backslash. Ask Question Asked 1 year, How do I limit the number of rows returned by an Oracle query after ordering? 399.

Use the following:

INSERT INTO B (GOOD_DATE)
  SELECT TO_DATE(BAD_DATE, 'DD/MM/YYYY')
    FROM A
    WHERE REGEXP_LIKE(BAD_DATE, '[0-9]+/[0-9]+/[0-9]+')

SQLFiddle here

Best of luck.

How to Replace Special Characters in Oracle SQL, , you can use a function like this: SELECT REGEXP_REPLACE(your_column, '[^0-9A-Za-z]', '') If replace_string is a CLOB or NCLOB, then Oracle truncates replace_string to 32K. The replace_string can contain up to 500 backreferences to subexpressions in the form , where n is a number from 1 to 9. If you want to include a backslash ( \) in replace_string, then you must precede it with the escape character,

REGEXP_EXTRACT, . A string function used in search operations for sophisticated pattern matching including repetition and alternation. the string to search for strings matching the regular expression. If replace_string is a CLOB or NCLOB, then Oracle truncates replace_string to 32K. The replace_string can contain up to 500 backreferences to subexpressions in the form , where n is a number from 1 to 9. If n is the backslash character in replace_string, then you must precede it with the escape character ( \\ ).

Oracle regular expression regexp_like, In Oracle SQL, you have three options for replacing special characters: Using the REPLACE function; Using the REGEXP_REPLACE function  The character n must be a digit from 1 to 9, designating the n th subexpression, numbered from left to right. The expression is invalid if the source string contains fewer than n subexpressions preceding the . For example, the regular expression ^(.*)\1$ matches a line consisting

Ben Brumm's Blog: How to Replace Special Charac, Oracle REGEXP_REPLACE function : The REGEXP_REPLACE function If n is the backslash character inreplace_string, then you must \n, Matches n number (digit 1 to 10) LINE FEED character enclosed between ( and ). Developers and DBAs get help from Oracle experts on: Replace backslash and double quotes inside a string using sqlldr. keep up to date with AskTOM via the

Oracle REGEXP_REPLACE function, Backslashes only need to be escaped if they're followed by a digit or another backslash. To insert \\ literally, use the string '\\\\'. While SQL does not require  Oracle follows the exact syntax and matching semantics for these operators as defined in the POSIX standard for matching ASCII (English language) data. For more complete descriptions of the operators, examples of their use, and Oracle multilingual enhancements of the operators, refer to Oracle Database Development Guide. Notes following the