Oracle regex replace (save only az)

Question

I have data in the last_name column.

This data comes from the Internet, and sometimes users copy the name insert from the dictionary document. This is a problem when the last name has one quote. Somehow, a single quote from a document with text is strange.

I want to write an oracle regular expression replacement in my select query such that it replaces everything in the last_name column, but just saves (az or AZ).

Is this doable?

Answer 1

Finally I went with this:

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

I keep from z to Z and one quote

Answer 2

By "weird" do you mean that this is not an ordinary single quote? Word has some really stupid characters that it uses by default (called "smart quotes" (google) ", which are a bit like standard quotes, but have different behavior when converting to and from ASCII and, for example, UTF-8

To identify them in SQL, make a choice using the ASCIISTR function and find a substring in the form <backslash><4 digits> (This works with a database with UTF-8 and simialr characters, I'm not sure if it will be returned to the database with ASCII parameters NLS)

 select asciistr(COLUMN) from table 

I used the following code to remove them in a recent job I was doing

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

NTN

Answer 3

You can use the regexp_replace operator, for example:

 select regexp_replace('foobar1000!!!!','[[:cntrl:]]|[[[:digit:]]|[[:punct:]]') from dual; REGEXP_REPLACE('FOOBAR1000!!!!','[[:CNTRL:]]|[[[:DIGIT:]]|[[:PUNCT:]]') ----------------------------------------------------------------------- foobar 

Answer 4

It is worth looking at the CONVERT function, which will do the conversion between character sets. You can convert to “simple” 7-bit ASCII characters to remove anomalies.

for instance

select convert ('êê', 'US7ASCII') from the double;