CODEWITHSUNDEEP
sqloracle-database
Sravanth Kothuri
Sravanth Kothuri

Reputation: 211

Find the accent data in table records

In a table, I have a column that contains a few records with accented characters. I want a query to find the records with accented characters.

If we have records like as below:

2ème édition
Natália
sravanth

query should pick these records:

2ème édition
Natália

Upvotes: 2

Views: 9128

Answers (4)

mivk
mivk

Reputation: 15009

The ASCIISTR function would be another way to find accented characters

ASCIISTR takes as its argument a string, or an expression that resolves to a string, in any character set and returns an ASCII version of the string in the database character set. Non-ASCII characters are converted to the form \xxxx, where xxxx represents a UTF-16 code unit.

So you can do something like

SELECT my_field FROM my_table
WHERE NOT my_field = ASCIISTR(my_field)

Or to re-use the demo from the accepted answer:

with t1(data) as (
  select '2ème édition' from dual union all
  select 'Natália' from dual union all
  select 'sravanth' from dual
)
select * from t1 where data != asciistr(data)

which would output the 2 rows with accents.

Upvotes: 4

Sravanth Kothuri
Sravanth Kothuri

Reputation: 211

with t1(data) as ( select '2ème édition' from dual union all select 'Natália' from dual union all select 'sravanth' from dual ) select * from t1 where REGEXP_like(ASCIISTR(data), '\ \ [[:xdigit:]]{4}');

DATA         
--------------
2ème édition  
Natália

Upvotes: 1

paparazzo
paparazzo

Reputation: 45096

Way harder than it seems on the surface as there is more than one way to create an accent. What I do is have a mirror column I call clean and scrub out all the accents on load.

See this question I asked some time ago normalized string

Upvotes: -1

Sentinel
Sentinel

Reputation: 6449

You can use the REGEXP_LIKE function along with a list of all the accented characters you're interested in:

with t1(data) as (
  select '2ème édition' from dual union all
  select 'Natália' from dual union all
  select 'sravanth' from dual
)
select * from t1 where regexp_like(data,'[àèìòùÀÈÌÒÙáéíóúýÁÉÍÓÚÝâêîôûÂÊÎÔÛãñõÃÑÕäëïöüÿÄËÏÖÜŸçÇßØøÅåÆæœ]');

DATA         
--------------
2ème édition  
Natália

Upvotes: 4

Related Questions