@All - Thanks for your help
1 [email protected]. , [email protected]@
In the above example, if you see the 1st row, the email address is invalid because of dot at the end of 1st email address and @ at the end of 2nd email address. In 2nd row, there is a ? in between email address.
Just wanted to know is there any way to handle there characters and remove those from email address using SQL function and update the same in table.
Thanks in advance.
you can also check a translate function
translate('my ,string@with .special chars','@,?. ', ' ')
translate() does exactly what you are asking for, if you have a specific list of characters you want to replace.You could nest multiple invokations of replace(), but this quickly becomes convoluted.
On the other hand, regexp_replace() comes handy for this:
regexp_replace(column_name, '@|,|\?|\.', ' ')
The pipe character (|) stands for or. The dot (.) and the question mark (?) are meaningful characters in regexes so they need to escaped with a backslash (\).
Something like this will "remove" everything but digits, letters and spaces (if that's what you wanted).
SQL> with test (col) as
2 (select 'This) is a se#nten$ce with. everything "but/ only 123 numbers, and ABC lett%ers' from dual)
3 select regexp_replace(col, '[^a-zA-Z0-9 ]') result
4 from test;
RESULT
-----------------------------------------------------------------------
This is a sentence with everything but only 123 numbers and ABC letters
SQL>
removeAllWrongCharacters. then there is no such function. you already have to make specifications, e.g. which characters and where they have to be removed. in your case you have to work with regular expressions