oracle regex replace (keep only a-z)

I have data in 开发者_如何学JAVAlast_name column.

This data comes from online and at times users are copy pasting the last name from a word document. This is a problem when a last name has a single quote. Somehow the single quote from a word document is weird.

I want to write an oracle regex replace in my select query such a way that it replaces everything in the last_name column but just keeps (a-z or A-Z).

Is this doable?

finally I went with this:

REGEXP_REPLACE(mbr_last_name,'[^a-zA-Z'']','') replaced_last_name 

I'm keeping a to z A to Z and a single quote

By 'weird' do you mean that it is not the normal single quote? Word has some really stupid characters that it uses by default (called 'Smart quotes (google)'. which look a bit like the standard quotes but have different behaviour when converting to and from ASCII and for example UTF-8

To identify them in SQL do a select using the function ASCIISTR and look for a sub-string in the form <backslash><4 digits> (This works with a DB with UTF-8 and simialr characters, I'm unsure about its return in a database with ASCII NLS parameters)

select asciistr(COLUMN) from table

I have used the following code to remove them in some recent work I have been doing

update jiraissue
set summary = replace(asciistr(summary), '\2013','-')
where asciistr(summary) like '%\2013%';`)

HTH

You can use the regexp_replace operator for instance:

select regexp_replace('foobar1000!!!!','[[:cntrl:]]|[[[:digit:]]|[[:punct:]]') from dual;

REGEXP_REPLACE('FOOBAR1000!!!!','[[:CNTRL:]]|[[[:DIGIT:]]|[[:PUNCT:]]') 
----------------------------------------------------------------------- 
foobar   

Worthwhile looking at the CONVERT function, which will do a conversion between character sets. You can convert to 'plain' 7-bit ASCII characters to remove anomalies.

For example

select convert('êê','US7ASCII') from dual;